Dear all,

Please copy and paste below query into query generator to get inventory aging for particular warehouse as well as item group name.

SELECT

T0.ITEMCODE , T0.ONHAND as ‘Total Qty’,

CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND END ’45 to 90 Days(Qty)’ , CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND*T0.AVGPRICE END ’45 to 90 Days(Value)’, CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND END ‘>90 Days(Qty)’, CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND*T0.AVGPRICE END ‘>90 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 AND T0.WhsCode ='[%0]’

AND T2.ITMSGRPNAM = ‘[%2]’

Hope helpful.

Thanks & Regards,

Nagarajan

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Prasanna s

    Hi Naga Rajan,

    I this below example your query will give wrong result,

    Purchase one – I have done purchase 01jan2013 – 100 qty

    Purchase Two – 01-june-2013 – 10 qty

    Now my instock 110 qty

    Now running the report on 02june2013

    As per your query I will get the output in 0- 45days 110qty

    But this is wrong, I should get 0-45 10qty and >90 100qty

    Correct me if am wrong……

    (0) 

Leave a Reply