Query Report – Inventory Aging
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
Dear sir pales help me chart of accounts queries
for ex:=
april may jun july in all mounts balancess in mount wise pivot table querys
5004 purchase of milk a/c
plase tell me
<removed by moderator>
venubabu
Hi naga srinu
I think this Query will full fill your requirement.
Declare @test VARCHAR(100)
Select @test= AcctCode from dbo.OACT S0 where S0.AcctCode= '[%0]'
select *
from
(select datename(month, T0.RefDate) As Mth,T1.[Account]
,SUM(T1.[Debit] - T1.[Credit]) as [Opening Balance]
from OJDT T0 INNER JOIN
JDT1 T1 ON T0.TransId = T1.TransId AND T1.[Account] = @test AND T0.RefDate between '20130401' and '20140331'
group by datename(month, T0.RefDate),T1.[Account]
) PP
pivot( sum([Opening Balance]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X
Swapnil Vichare
your Query report is ok
THANKING YOU
G VENU BABU
And another problem
Dear sir pales help me chart of accounts queries
for ex:=
april may jun july in all mounts balancess in mount wise pivot table querys
5004 purchase of milk a/c BANALNCESS IN B.P CODE WISE
plase tell me
VENUBABU
Hi,
Please explain what you required.
purchase of milk a/c BANALNCESS IN B.P CODE WISE ? ? ?
chart of account 5004 balancess in year (mounth wise) report is ok
but another Query is
1. chart of account balance's in year (mounts wise balance's) project code wise query
2.chart of account balance's in year (mounts wise balances) vendor code wise query (based on journal eatery)
please tell me
venubabu
plase help me
venubabu
This query gives you project wise & Account wise report.
If you required a report for all accounts and required selection of Project Code then just remove "AND T1.[Account] = @AccCode" From Join Between OJDT & JDT1
Declare @AccCode VARCHAR(100)
Select @AccCode= AcctCode from dbo.OACT S0 where S0.AcctCode= '[%0]'
Declare @PrjCode VARCHAR(100)
Select @PrjCode= PrjCode from dbo.OPRJ S1 where S1.PrjCode= '[%1]'
select *
from
(select datename(month, T0.RefDate) As Mth,T1.[Account]
,SUM(T1.[Debit] - T1.[Credit]) as [Opening Balance]
from OJDT T0 INNER JOIN
JDT1 T1 ON T0.TransId = T1.TransId AND T1.[Account] = @AccCode AND T0.RefDate between '20130401' and '20140331' Inner Join
OACT T2 ON T1.Account=T2.AcctCode Left Outer Join
OPRJ T3 ON T2.Project=T3.PrjCode
Where T3.PrjCode =@PrjCode
group by datename(month, T0.RefDate),T1.[Account]
) PP
pivot( sum([Opening Balance]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X
DEAR SIR REPORT IS EXECUTE BUT OUT PUT NO DATA
HELP ME CHANGE QUERY
MY PROBLEM
1.ONE GL A/C CODE IS SELECTED DISPLAY THE PROJECT CODE
WISE,ALL MOUNT'S BALANCES IN SAP BUSINESS ON QUERY
(OR)
2.GL A/C EX:- 5004 IS FIXED AND PROJECT CODE EX:-(CHI,MAP,GKD, NVP) IS FIXED
AND DISPLAY REPORT TITLES (PROJECT CODE ,ALL MONTHS)
3.ONE GL A/C CODE IS SELECTED ,PROJECT CODE SELECTED
VENDOR CODE WISE MOUTH (Business Partners)WISE BALANCES BASE IN (Journal Entry) QUERY
PEASE
HELP ME CHANGE QUERY
VENUBABU
Declare @AccCode VARCHAR(100)
Select @AccCode= AcctCode from dbo.OACT S0 where S0.AcctCode= '[%0]'
select *
from
(select datename(month, T0.RefDate) As Mth,T1.[Account]
,SUM(T1.[Debit] - T1.[Credit]) as [Opening Balance]
from OJDT T0 INNER JOIN
JDT1 T1 ON T0.TransId = T1.TransId AND T1.[Account] = @AccCode AND T0.RefDate between '20130401' and '20140331' Inner Join
OACT T2 ON T1.Account=T2.AcctCode Left Outer Join
OPRJ T3 ON T2.Project=T3.PrjCode
Where (T3.PrjCode ='[%2]' OR '[%2]' =' ')
group by datename(month, T0.RefDate),T1.[Account]
) PP
pivot( sum([Opening Balance]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X
This Query will gives you proper report But it is important to select Proper Project Code for that selected Account.
There is other provision in this query, If you does not know project code for that account then leave project code field blank .
Is you use segmentation in Cart of account in your company.
Please give me your Cart of account window screen short.
QUERY IS NOT WORKING SIR
5004 – IS PURCHASE OF MILK A/CIN CHART OF ACCOUNT CODE
MY PURCHASE OF MILK IN BRANCHS IS THER
EX: APRIAL MOUNTH
5004- CHART OF ACCOUNT BALANCESS IN 100000/- MAIN HEAD TOTAL
BRANCHS PROJECTCODE OR GROUPMAME WISE
GROUPNAME PROJECT CODE AMOUNT
CHITTOOR- CHI 50000
MADANAPALLI MAP 25000
GURRAMKONDA GKD 25000
TOTAL 100000
THIS QUERY IS EXECUT IN CURRANT ANSWER IN BETWEEN DAYS
SELECT T0.[Project],(sum(T0.Debit)-sum(T0.Credit))as 'value' FROM JDT1 T0 WHERE T0.Account='5004' and T0.RefDate>=[%0] and T0.RefDate<=[%1] GROUP BY T0.Project
#
Project Code
value
1
CHI
2,349,671.12
2
GAV
926,025.83
3
GKD
1,146,867.70
4
GPP
434,241.00
5
HYD
979,550.00
6
KLK
651,926.00
7
KOD
4,661,521.33
8
KSP
3,208,826.18
9
MAP
2,202,668.56
10
NVP
1,368,536.86
11
SHA
3,129,242.22
12
VAR
3,672,568.59
BUT MY PROBLEM IS MOUNTH WISE BALANCESS IN PIOVT TABLE QUERY IN ALL MOUNTHES IN BASE ON UPQUERY
VENUBABU
This Query definitely solve your issue for Project wise balance of chart of account 🙂
Declare @AccCode VARCHAR(100) Select @AccCode= AcctCode from dbo.OACT S0 where S0.AcctCode= '[%0]' select * from (select datename(month, T0.RefDate) As Mth,T1.[Account],T1.[Project] ,SUM(T1.[Debit] - T1.[Credit]) as [Opening Balance] from OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId AND T1.[Account] = @AccCode AND T0.RefDate between '20130401' and '20140331' Inner Join OACT T2 ON T1.Account=T2.AcctCode Left Outer Join OPRJ T3 ON T2.Project=T3.PrjCode Where (T3.PrjCode ='[%2]' OR '[%2]' =' ') group by datename(month, T0.RefDate),T1.[Account],T1.[Project] ) PP pivot( sum([Opening Balance]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X/body>
This Query solve my issue for Project wise balance of chart of account
i am use fill query sir
THANKING YOU
venubabu
Till now i doesn't understand your 2 Query requirement.
What u exactly want? 😕
Please explain 2nd query issue in brief.
Select P.CardCode,P.CardName,
[1] as [Jan],
[2] as [Feb],
[3] as [Mar],
[4] as [Apr],
[5] as [May],
[6] as [Jun],
[7] as [Jul],
[8] as [Aug],
[9] as [Sep],
[10] as [Oct],
[11] as [Nov],
[12] as [Dec]
from((select T1.CardCode ,T1.CardName,(sum(T0.Debit)-sum(T0.Credit))as 'value' ,month(t0.docdate) as 'Month'
from JDT1 T0 Inner join OCRD T1 on T1.CardCode=T0.ShortName
inner join OCRG T2 on T1.GroupCode=T2.GroupCode
where T0.ContraAct='2657' and T2.GroupName=[%0]
and T1.CardType='S' and T2.GroupType='S'
group by T1.CardCode,T1.CardName) as A
Pivot((sum(T0.Debit)-sum(T0.Credit))as 'value' FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
order by T1.CardCode
QUERY IS PROBLEM SIR
THIS Query is execute me
solow the vendor wise balancess report problem
plase help me
Select * from(select T1.CardCode ,T1.CardName,(sum(T0.Debit)-sum(T0.Credit))as 'value'
from JDT1 T0 Inner join OCRD T1 on T1.CardCode=T0.ShortName
inner join OCRG T2 on T1.GroupCode=T2.GroupCode
where T0.ContraAct='2657' and T2.GroupName=[%0]
and T1.CardType='S' and T2.GroupType='S'
group by T1.CardCode,T1.CardName) as A
where A.value<>0
order by A.CardCode
this query total balances in all mount's but i am expect query in mounts wise balancess query
pales help me
venubabu
This query will solve your issue for monthwise balance of BP, Just select Group name for it.
select *
from
(SELECT datename(month, T0.RefDate) As Mth,T2.CardCode,T2.CardName,SUM(T1.Credit-T1.Debit) AS [Value] FROM
OJDT T0 INNER JOIN
JDT1 T1 ON T0.TransId = T1.TransId AND T1.ContraAct='2657' Inner Join
OCRD T2 ON T1.ShortName=T2.CardCode AND T2.CardType='S' Inner Join
OCRG T3 ON T2.GroupCode=T3.GroupCode AND T3.GroupType='S'
Where T3.GroupName ='[%0]'
group by datename(month, T0.RefDate),T2.CardCode,T2.CardName
) PP
pivot( sum([Value]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X
If you want total report for all groups then use this query and keep Group name field blank .
select *
from
(SELECT datename(month, T0.RefDate) As Mth,T2.CardCode,T2.CardName,T3.GroupName,SUM(T1.Credit-T1.Debit) AS [Value] FROM
OJDT T0 INNER JOIN
JDT1 T1 ON T0.TransId = T1.TransId AND T1.ContraAct='2657' Inner Join
OCRD T2 ON T1.ShortName=T2.CardCode AND T2.CardType='S' Inner Join
OCRG T3 ON T2.GroupCode=T3.GroupCode AND T3.GroupType='S'
Where (T3.GroupName ='[%0]' OR '[%0]'=' ')
group by datename(month, T0.RefDate),T2.CardCode,T2.CardName,T3.GroupName
) PP
pivot( sum([Value]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X
i think your both problems r solved, if u have any issues then tell me about that.
Declare @AccCode VARCHAR(100)
Select @AccCode= AcctCode from dbo.OACT S0 where S0.AcctCode= '[%0]'
select *
from
(select datename(month, T0.RefDate) As Mth,T1.[Account],T1.[Project]
,SUM(T1.[Debit] - T1.[Credit]) as [Opening Balance]
from OJDT T0 INNER JOIN
JDT1 T1 ON T0.TransId = T1.TransId AND T1.[Account] = @AccCode AND T0.RefDate between '20130401' and '20140331' Inner Join
OACT T2 ON T1.Account=T2.AcctCode Left Outer Join
OPRJ T3 ON T2.Project=T3.PrjCode
Where (T3.PrjCode ='[%2]' OR '[%2]' =' ')
group by datename(month, T0.RefDate),T1.[Account],T1.[Project]
) PP
pivot( sum([Opening Balance]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X
If you want total report for all groups then use this query and keep Group name field blank .
select *
from
(SELECT datename(month, T0.RefDate) As Mth,T2.CardCode,T2.CardName,T3.GroupName,SUM(T1.Credit-T1.Debit) AS [Value] FROM
OJDT T0 INNER JOIN
JDT1 T1 ON T0.TransId = T1.TransId AND T1.ContraAct='2657' Inner Join
OCRD T2 ON T1.ShortName=T2.CardCode AND T2.CardType='S' Inner Join
OCRG T3 ON T2.GroupCode=T3.GroupCode AND T3.GroupType='S'
Where (T3.GroupName ='[%0]' OR '[%0]'=' ')
group by datename(month, T0.RefDate),T2.CardCode,T2.CardName,T3.GroupName
) PP
pivot( sum([Value]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X
This Query solve my issue for vendor balance
i am use fill query sir
THANKING YOU
venubabu
dear
sir
my name is venubabu
my qualification is m,com
i am accountant in jyothi dairy pvt ltd Hyderabad
4 years in working in sap business one software in accounting purpose my job
i am change this job software side (implantation side sap business one)
basic qualification tell me in implantation side sap business one
what are you course training tell me sir
pales your suggestion tell me
venubabu
Hi Naga Srinu,
Please check with below link for SAP B1 training:
https://training.sap.com/in/en/curriculum/b1_90-sap-business-one-90-g-en
Thanks & Regards,
Nagarajan
dear sir
help me
query manager in queries is execute report BUT EXECUTE direct in FIXED REPORT (PLD,EXCEL) PLASE TELL ME
VENUBABU
Hi,
I don't understand your problem.
To get answers for problem, you should post as discussion.
Regards,
Nagarajan
SIR
MY problem is queries is setting in execute to excel format or any stranded report is pose-bull
tell me in process
venubabu
Hi,
Do you know write query in SAP B1?
yas
I don't exactly understand your problem. 😕
I think you required to Export the output of your report in Excel format.
Run your Query in Query manager and click on Excel icon in tool bar to Export your report in Excel Format.
DEAR
sir
please help in Business Partner Master Data query report
BP CODE ,BP NAME ,OFFSET ACCOUNT 5657, 4006 ,1607 ,1662, 1686 BALANCES
EX:- SELECTED BETWEEN DAYS 01.04.13 TO 15.04.13
CS001-VENUBABU- 5000- 600- 8000- 4000- 1500
IN BETWEEN DAY
HELP ME
VENUBABU
Raise in questing not here read rules of engagement first
dear sir plase help me
Select * from(select T1.CardCode ,T1.CardName,(sum(T0.Debit)-sum(T0.Credit))as 'purchase',
(sum(T0.Debit)-sum(T0.Credit))as 'advance'
From JDT1 T0 Inner join OCRD T1 on T1.CardCode=T0.ShortName
inner join OCRG T2 on T1.GroupCode=T2.GroupCode
(where T0.Account='2511' as 'purchase' and where T0.Account='1857' as 'advance'),
where T2.GroupName=[%0] and ((T0.RefDate>=[%1] and T0.RefDate<=[%2]) or (T0.RefDate<[%1]))
and T1.CardType='S' and T2.GroupType='S'
group by T1.CardCode,T1.CardName
order by A.CardCode
this report not excute sir
my problem vendor chart of accountent balaancess in bussiness partener master data parchase ac 2511 and advance ac 1857 two account balancess is there query is excute 2511 balancess and 1857 balancess
ex:-total bp balance 15000/- 2511 ac -10000/-and 1857 ac +25000/-
plase help me this report
What a complete Inventory Aging Report. Thumbs up.
Hi,
Nice post.
Thanks & Regards
Ankit Chauhan
Hi Sap Gurus,
Could you please provide info cube & Multi provider cube on inventory ageing report
regards,
Chand
Hi Kennedy
This report gives stock aging details of old stock also. For eg. My current stock qty is 500 is in 0-30 days but my overall aging is 30-60 days is 2000 Qty. This report shows data for both 0-30 days as 500 qty and 30-60 days as 2000 Qty.
Please let me know how to make visible only the 0-30 days Stock Qty which is 500 when I choose Particular date.
This is the Query Which I tested
Declare @a char(60)
Set @a = (select distinct t20.docdate from oinm t20 where t20.docdate = '20181231')
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))
) [Pric],
(select (sum(isnull(t11.inqty,0)) - sum(isnull(t11.outqty,0)) ) from oinm t11 where (t11.itemcode = (t0.itemcode))
and (t11.docdate <= '20181231' )) [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
group by t0.itemcode,t0.docdate,t0.Warehouse
)a
)b
)c where c.bal>0 and c.Code='6PAB-84x130PNA'
group by c.code, c.name