Hi Experts,
I have written a query to get an Inventory Ageing reports by 0-30days, 31-60days, 61-90days, 91-120days and 121+.
The query shows Quantity and the Value (QTY * Average Price) by Age 0-30days, 31-60days, 61-90days etc.
SELECT T0.ITEMCODE , T0.ONHAND as 'Total Qty',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<30 THEN T0.ONHAND END '<30 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<30 THEN T0.ONHAND*T0.AVGPRICE END '<30 Days(Value)',
CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 31 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 60 THEN T0.ONHAND END '31 to 60 Days(Qty)' ,
CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 31 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 61 THEN T0.ONHAND*T0.AVGPRICE END '31 to 60 Days(Value)',
CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 61 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND END '61 to 90 Days(Qty)' ,
CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 61 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND*T0.AVGPRICE END '61 to 90 Days(Value)',
CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 91 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 120 THEN T0.ONHAND END '91 to 120 Days(Qty)' ,
CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 91 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 120 THEN T0.ONHAND*T0.AVGPRICE END '91 to 120 Days(Value)',
CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 121 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) > 121 THEN T0.ONHAND END '121+ Days(Qty)' ,
CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 121 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) > 121 THEN T0.ONHAND*T0.AVGPRICE END '121+ Days(Value)'
FROM OITW T0 INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE
INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD
WHERE
T0.ONHAND>0
I want to know how i can get the Final Totals of all Values by 0-30days, 31-60days, 61-90days etc.