Skip to Content
0
Dec 28, 2018 at 09:39 AM

Inventory Aging Report

218 Views Last edit Dec 28, 2018 at 09:59 AM 5 rev

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!

Attachments