Goods Received ageing report

23 Views

Hello Experts,

I am trying to compose a query to show how long stock has been sitting in a warehouse for...

I need to change the last purchase date (T1.LASTPURDAT) to goods received date ? We would place a large purchase order for x10,000 and call off n smaller quantities.

SELECT distinct T0.itemcode , t1.itemname, T0.ONHAND as 'Total Qty',

CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<2 THEN T0.ONHAND END '<2 Days(Qty)',

CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 2 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 5 THEN T0.ONHAND END '2 to 5 Days(Qty)' ,

CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 5 THEN T0.ONHAND END '>5 Days(Qty)'

FROM OITW T0

INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE

left join ibt1 t3 on t3.itemcode = t0.itemcode and t3.whscode = t0.whscode

WHERE T0.WhsCode = 95 and T0.ONHAND>0

Please help.

Thanks

Gary