Inventory Aging Report
Declare @a char(60)
Set @a = (select distinct t20.docdate from oinm t20 where t20.docdate = '[%2]')
Select distinct c.code, c.name
,SUM(c.bal) [Bal],sum(c.Val) [Val],
(case when avg(c.[FullStock]) > 0 then avg(c.[Pric]) / avg(c.[FullStock]) end ) [AvgPrice],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then SUM(c.bal) else SUM(c.[0-30 Days]) end) [0-30 Days],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >= SUM(c.bal) then (SUM(c.bal) - sum(c.[0-30 Days]))
else sum(c.[30-60 Days]) end)end) [30-60 Days],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then
(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]))) else
sum(c.[60-90 Days])end)end) end) [60-90 Days],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then
(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]))) else sum(c.[90-180 Days])
end)end) end) end) [90-180 Days],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) +
sum(c.[90-180 Days]) + sum(c.[180-360 Days]) ) >= SUM(c.bal) then
(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])))
else sum(c.[180-360 Days])end)end)end)end)end) [180-360 Days],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else sum(c.[Above 360 Days])
end)end)end) [Above 360 Days]
from (
select b.code, b.name,
b.Bal, b.Val,b.[Pric],b.[FullStock],
isnull(case when b.days <=30 then b.Bal end,0)[0-30 Days] ,
isnull(case when b.days <= 30 then b.val end,0)[0-30 Days-Value] ,
isnull(case when b.days between 31 and 60 then b.Bal end,0) [30-60 Days],
isnull(case when b.days between 61 and 90 then b.Bal end,0) [60-90 Days],
isnull(case when b.days between 91 and 180 then b.Bal end,0) [90-180 Days],
isnull(case when b.days between 181 and 360 then b.Bal end,0) [180-360 Days],
isnull(case when b.days >= 361 then b.Bal end,0) [Above 360 Days]
from
(
select a.code,a.name,
a.bal,a.val,datediff(dd,dt,@a)'days',a.[Receipt],a.[Pric],a.[FullStock]
from
(
select max(t0.itemcode)'Code',max(t0.Dscription)'Name',
(select sum(isnull(t11.transvalue,0)) from oinm t11 where (t11.itemcode = (t0.itemcode))
and (t11.docdate <= '[%2]' )) [Pric],
(select (sum(isnull(t11.inqty,0)) - sum(isnull(t11.outqty,0)) ) from oinm t11 where (t11.itemcode = (t0.itemcode))
and (t11.docdate <= '[%2]' )) [FullStock],
--(t0.Warehouse)'Wh',
(sum(isnull(t0.inqty,0))) [Receipt],
(sum(isnull(t0.inqty,0)) - sum(isnull(t0.outqty,0)) )'Bal',sum(isnull(t0.transvalue,0))'Val',(t0.docdate)'dt'
from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode inner join oitb ob on ob.itmsgrpcod = t1.itmsgrpcod
where (ob.itmsgrpnam = '[%1]')
and (t0.docdate <= '[%2]')
group by t0.itemcode,t0.docdate
)a
)b
)c where c.bal>0
group by c.code, c.name
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
3 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 |