Skip to Content

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

35 Comments
You must be Logged on to comment or reply to a post.
  • 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

        • 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

          • 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 . 

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

          • 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.

            •    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

            pivot( sum([Opening Balance]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X

            • 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


          • 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    

  • 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

  • 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

  • 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