Skip to Content
0
Aug 04, 2016 at 11:59 PM

Inventory Aging Query

2803 Views

Hello SAP Gurus,

Can someone please help me add AvgPrice column to the following Query?

select b.code 'Item No.', b.name 'Description', b.Wh, b.Bal 'Total Qty', b.Val 'Total Value',

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

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

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

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

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

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

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

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

Thank you.

Kind regards,

Larry T.