Skip to Content
Author's profile photo Nagarajan K
Nagarajan K
October 2, 2013 Less than a 1 minute read

Inventory aging report- By warehouse

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

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Prasanna s
      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......

      Author's profile photo Nagarajan K
      Nagarajan K
      Blog Post Author

      Hi,

      Please post your query result here.

      Thanks & Regards,

      Nagarajan

      Author's profile photo prabakaran R
      prabakaran R

      Hi Nagarajan,

       

      I want to take the Aging report based on Receipt from production. This is possible?