Skip to Content
0
Former Member
Nov 08, 2013 at 06:48 AM

Inventory Aging Report

83 Views

Hi Experts,

Is there possible to amend query below (found from others post) to include posting date as parameter instead of warehouse?

For example, I have to generate inventory aging report from 01/07/12 to 30/06/13 (for financial period).

select b.code, b.name, b.Wh, b.Bal, b.Val,

isnull(case when b.days <30 then b.bal end,0)'0-30 Days' ,

isnull(case when b.days between 30 and 60 then b.bal end,0) '30-60 Days',

isnull(case when b.days between 60 and 90 then b.bal end,0) '60-90 Days',

isnull(case when b.days between 90 and 120 then b.bal end,0) '90-120 Days',

isnull(case when b.days between 120 and 150 then b.bal end,0) '120-150 Days',

isnull(case when b.days between 150 and 180 then b.bal end,0) '150-180 Days',

isnull(case when b.days >180 then b.bal end,0) 'Above 180 Days'

from (

select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())'days'

from (

select max(t0.itemcode)'Code',max(t0.Dscription)'Name',

max(t0.Warehouse)'Wh',

sum(t0.inqty-t0.outqty)'Bal',sum(t0.transvalue)'Val',max(t0.docdate)'dt'

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

where t0.warehouse='[%1]'

group by t0.itemcode

)a

)b order by code

Is there any others solution or query to fulfill my requirement? Kindly advise.

Thanks You.

Mun