DeadStock Sql Query

 declare @DataAreaId nvarchar(4) = 'GH01',

@datePeriod int = 180

 

 

select s.DATAAREAID,s.ITEMID,i.GOD_BRANDMANAGER_ID, i.PmfProductType, i.GOD_INACTIVE,isnull(t1.lastdate,'03-01-2020') [LASTDATE],m.UNITID, sum(s.AVAILPHYSICAL) [AVAILPHYSICAL] ,sum(s.RESERVPHYSICAL) [RESERVORDERED],

          sum(s.AVAILPHYSICAL) + sum(s.RESERVPHYSICAL) [TOTALPHYSICAL]

, sum(s.PHYSICALVALUE)[PHYSICALVALUE] , sum(s.POSTEDVALUE) [POSTEDVALUE],  sum(s.PHYSICALVALUE) + sum(s.POSTEDVALUE) [TOTALCOST]

 

from INVENTSUM s

LEFT join (

          select tr.DATAAREAID, tr.ITEMID, max(tr.DATESTATUS) [LastDate]

          from INVENTTRANS tr

          inner join INVENTTRANSORIGIN o on o.RECID = tr.INVENTTRANSORIGIN

          where tr.DATAAREAID = @DataAreaId and o.REFERENCECATEGORY = 0 and tr.STATUSISSUE <> 0

          group by tr.DATAAREAID,tr.ITEMID

          ) t1 on t1.DATAAREAID = s.DATAAREAID and t1.ITEMID = s.ITEMID

          inner join INVENTTABLEMODULE m on m.DATAAREAID = s.DATAAREAID and m.ITEMID = s.ITEMID and m.MODULETYPE = 0

  inner join inventTable I on I.ITEMID = s.ITEMID and i.DATAAREAID= s.DATAAREAID

 

 

where s.DATAAREAID = @DataAreaId  and s.CLOSED = 0 and isnull(t1.LastDate,'03-01-2020') <= GETDATE() - @datePeriod

 

group by s.DATAAREAID,s.ITEMID,isnull(t1.lastdate,'03-01-2020'),m.UNITID,i.GOD_BRANDMANAGER_ID, i.GOD_INACTIVE, i.PmfProductType

Having sum(s.AVAILPHYSICAL) + sum(s.RESERVPHYSICAL) > 0