Skip to Content
Author's profile photo Kennedy Thomas

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

Assigned tags

      35 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Hi,

      Please explain what you required.

      purchase of milk a/c BANALNCESS IN B.P CODE WISE  ? ? ?

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      plase help me

      venubabu

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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 . 

      Author's profile photo Former Member
      Former Member

      Is you use segmentation in Cart of account in your company.

      Please give me your Cart of account window screen short.

      Author's profile photo Former Member
      Former Member

      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


      Author's profile photo Former Member
      Former Member

      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>

      Author's profile photo Former Member
      Former Member

      This Query  solve my issue for Project wise balance of chart of account

      i am use fill query sir

      THANKING YOU

      venubabu



      Author's profile photo Former Member
      Former Member

      Till now i doesn't understand your 2 Query requirement.

      What u exactly want? 😕

      Please explain 2nd query issue in brief.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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


      Author's profile photo Former Member
      Former Member

      This Query  solve my issue for vendor  balance

      i am use fill query sir

      THANKING YOU

      venubabu

      Author's profile photo Former Member
      Former Member

      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    

      Author's profile photo Nagarajan K
      Nagarajan K

      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

      Author's profile photo Former Member
      Former Member

      dear sir

      help me

      query manager in queries is execute report  BUT EXECUTE direct in FIXED REPORT (PLD,EXCEL)  PLASE TELL ME

      VENUBABU

      Author's profile photo Nagarajan K
      Nagarajan K

      Hi,

      I don't understand your problem.

      To get answers for problem, you should post as discussion.

      Regards,

      Nagarajan

      Author's profile photo Former Member
      Former Member

      SIR

      MY problem is queries is setting in execute to excel format or any stranded report is pose-bull

      tell me in process

      venubabu            

      Author's profile photo Nagarajan K
      Nagarajan K

      Hi,

      Do you know write query in SAP B1?

      Author's profile photo Former Member
      Former Member

      yas

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Kennedy T
      Kennedy T
      Blog Post Author

      Raise in questing not here read rules of engagement first

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Adrian Lee
      Adrian Lee

      What a complete Inventory Aging Report. Thumbs up.

      Author's profile photo Ankit Chauhan
      Ankit Chauhan

      Hi,

      Nice post.

      Thanks & Regards

      Ankit Chauhan

      Author's profile photo Former Member
      Former Member

      Hi Sap Gurus,

      Could you please provide info cube & Multi provider cube on inventory ageing report

      regards,

      Chand

      Author's profile photo Anand Ram
      Anand Ram

      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