SQL View for Items not sold last six months I want to create a view where I would like to get a list of Items that are not sold in last 6 months. I tried couple of approaches but none is working correct.
Please guide me. Here are 2 approaches:
I cannot make temp tables as this is a view?

select distinct a.ItemID, a.TranDate Into #Itemslast6months 
from SalesDataMart a
where TranDate &gt;= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)

Select distinct m.ITMNO_0 into #ItemsNotSoldLast6Months
from ITEMMASTER  as m
Where not exists (select ItemID
                    from #Itemslast6months as B
                    where m.ITMNO_0 = B.ItemID)


Select n.ITMNO_0, s.[Description], max(s.TranDate) last_Transaction_Date
from #temsNotSoldLast6Months n
join SalesDataMart s on n.ITMNO_0 = s.ItemID
group by n.ITMNO_0, s.[Description]


2nd Approach:

 WITH ItemsSoldLast6Months (ItemID, TranDate)
      AS
      (
        select distinct a.ItemID, a.TranDate 
        from SalesDataMart a
        where TranDate &gt;= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)

      )

  WITH ItemsNotSoldLast6Months (ItemNO)
      AS
      (
      Select distinct m.ITMNO_0 
from ITEMMASTER  as m
Where not exists (select ItemID
                    from ItemsSoldLast6Months as B
                    where m.ITMNO_0 = B.ItemID)

    )


Select n.ITMNO_0, s.[Description], max(s.TranDate) last_Transaction_Date
from #temsNotSoldLast6Months n
join SalesDataMart s on n.ITMNO_0 = s.ItemID
group by n.ITMNO_0, s.[Description]
Is this what you're looking for?

DECLARE @ItemMaster TABLE (ItemNo INT)
DECLARE @SalesDataMart TABLE (ItemId INT,TranDate DATE)

insert into @ItemMaster (ItemNO) values (1)
insert into @ItemMaster (ItemNO) values (2)

insert into @SalesDataMart (ItemId, TranDate) values (1,'2017-02-17')
insert into @SalesDataMart (ItemId, TranDate) values (2,'2016-02-17')
SELECT *
FROM @ItemMaster itmmst
WHERE NOT EXISTS (
        SELECT *
        FROM @SalesDataMart
        WHERE itemid = itmmst.itemno
            AND Trandate &gt;= Dateadd(month, - 6, sysdatetime())
        )