Skip to Content

Inventory Aging Report

Hi All,

I have develop the below query to take inventory aging report, Almost i got a solution. Here i required your suggestion to complete this report. The below is my query,

select Distinct T0.ItemCode,T0.OnHand,

CASE WHEN Datediff(day, T1.[InDate],GETDATE())<45 THEN (select count(T1.InDate) where Datediff(day, T1.[InDate],GETDATE())<45 and T1.Status='0' ) END '<45 Days(Qty)' ,

CASE WHEN DateDiff(day, T1.[InDate],GETDATE()) >= 45 AND DateDiff(day, T1.[InDate],GETDATE()) < 90 THEN (select count(T1.InDate) where DateDiff(day, T1.[InDate],GETDATE()) >= 45 AND DateDiff(day, T1.[InDate],GETDATE()) < 90 and T1.Status='0' ) END '45 to 90 Days(Qty)' ,

CASE WHEN DateDiff(day, T1.[InDate],GETDATE()) >= 90 AND DateDiff(day, T1.[InDate],GETDATE()) <180 THEN (select count(T1.InDate) where DateDiff(day, T1.[InDate],GETDATE()) >= 90 AND DateDiff(day, T1.[InDate],GETDATE()) <180 and T1.Status='0' ) END '90 to 180 Days(Qty)' ,

CASE WHEN Datediff(day, T1.[InDate],GETDATE()) >= 180 THEN (select count(T1.InDate) where Datediff(day, T1.[InDate],GETDATE()) >= 180 and T1.Status='0' ) END '>180 Days(Qty)'

from OITM T0 Inner join OSRI T1 on T0.ItemCode=T1.ItemCode

Where T0.OnHand>'0' and T0.ItemCode='R31-110-1039'

Group By T0.ItemCode,T0.OnHand,T1.InDate,T1.Status

Below attachment is my requirement

inventory-aging.png


Cheers!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 09 at 05:59 AM

    Hi All,

    I got the answer for my question. The below report will give you the exact inventory aging for Serial number based item's. If you want to take the aging for batch number items, then change this query as per your requirement,

    //Inventory Aging Report for Serial Number Item//

    SELECT
    T0.ItemCode,
    T0.OnHand,
    (SELECT COUNT(1) FROM OSRI WHERE DATEDIFF(DAY, InDate, GETDATE()) < 45 AND Status = 0 AND ItemCode = T0.ItemCode) AS ‘<45 Days(Qty)’,
    (SELECT COUNT(1) FROM OSRI WHERE DATEDIFF(DAY, InDate,GETDATE()) >= 45 AND DATEDIFF(DAY, InDate, GETDATE()) < 90 AND Status=’0′ AND ItemCode = T0.ItemCode) AS ’45 to 90 Days(Qty)’,
    (SELECT COUNT(1) FROM OSRI WHERE DATEDIFF(DAY, InDate,GETDATE()) >= 90 AND DATEDIFF(DAY, InDate, GETDATE()) < 180 AND Status=’0′ AND ItemCode = T0.ItemCode) AS ’90 to 180 Days(Qty)’ ,
    (SELECT COUNT(1) FROM OSRI WHERE DATEDIFF(DAY, InDate,GETDATE()) >= 180 AND Status=’0′ AND ItemCode = T0.ItemCode) AS ‘>180 Days(Qty)’
    FROM
    OITM T0
    WHERE
    T0.OnHand > 0

    AND T0.ManSerNum = ‘Y’

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 30, 2018 at 08:18 AM

    Dear Prabakaran,

    Try This Query if the item stock is located in two different warehouse then the line will split for the same item,

    SELECT DISTINCT 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()) >= 30 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 60 THEN T0.ONHAND END '30 to 60 Days(Qty)' , CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 30 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 60 THEN T0.ONHAND*T0.AVGPRICE END '30 to 60 Days(Value)', CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 60 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 120 THEN T0.ONHAND END '60 to 120 Days(Qty)' , CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 60 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 120 THEN T0.ONHAND*T0.AVGPRICE END '60 to 120 Days(Value)', CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 120 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 180 THEN T0.ONHAND END '120 to 180 Days(Qty)' , CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 120 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 180 THEN T0.ONHAND*T0.AVGPRICE END '120 to 180 Days(Value)', CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 180 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 240 THEN T0.ONHAND END '180 to 240 Days(Qty)' , CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 180 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 240 THEN T0.ONHAND*T0.AVGPRICE END '180 to 240 Days(Value)', CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 240 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 300 THEN T0.ONHAND END '240 to 300 Days(Qty)' , CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 240 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 300 THEN T0.ONHAND*T0.AVGPRICE END '240 to 300 Days(Value)', CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 300 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 360 THEN T0.ONHAND END '300 to 360 Days(Qty)' , CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 300 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 360 THEN T0.ONHAND*T0.AVGPRICE END '300 to 360 Days(Value)', CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 360 THEN T0.ONHAND END '>360 Days(Qty)', CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 360 THEN T0.ONHAND*T0.AVGPRICE END '>360 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 GROUP BY T0.ITEMCODE,T1.LASTPURDAT,T0.ONHAND,T0.AVGPRICE

    Regards

    Dinesh

    Add comment
    10|10000 characters needed characters exceeded