**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.[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 YOUG VENU BABUAnd 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 wisequery2.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 & JDT1Declare @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 QUERYMY 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:-

5004IS FIXED AND PROJECT CODE EX:-(CHI,MAP,GKD, NVP) IS FIXEDAND 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 accounti am use fill query sir

THANKING YOUvenubabu

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 querypales help mevenubabu

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.1) 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

2) This query will solve your issue for monthwise balance of BP, Just select Group name for it.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 balancei am use fill query sir

THANKING YOUvenubabudear

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 ChauhanHi 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