Skip to Content
Author's profile photo Former Member

Query for date wise closing balance

Hi Experts.

The below query will give date wise closing balance which items are having  transaction.

select  * from (select T1.Itemcode,t2.ItmsGrpNam,isnull(sum(T0.inqty-T0.OutQty),0) ‘Bal’  ,datepart(d,docdate) as [dates]

from   OIVL t0 left outer join OITM t1  ON T0.ItemCode=T1.ItemCode

left outer join OITB t2 on t1.ItmsGrpCod=t2.ItmsGrpCod

where   T0.DOCDATE>=[%0] AND T0.DOCDATE<=[%1]

group by T1.Itemcode,T0.DocDate , t2.ItmsGrpNam

) as s

pivot

(sum(bal)for [dates] in

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],

[11],[12],[13],[14],[15],[16],[17],[18],[19],[20]

,[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]

)) AS  pvt

You can use below query if you want for all items .

select  * from (select T1.Itemcode,t2.ItmsGrpNam,isnull(sum(T0.inqty-T0.OutQty),0) ‘Bal’  ,datepart(d,docdate) as [dates]

from   OIVL t0 left outer join OITM t1  ON T0.ItemCode=T1.ItemCode

left outer join OITB t2 on t1.ItmsGrpCod=t2.ItmsGrpCod

where   T0.DOCDATE>=[%0] AND T0.DOCDATE<=[%1]

group by T1.Itemcode,T0.DocDate , t2.ItmsGrpNam

) as s

pivot

(sum(bal)for [dates] in

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],

[11],[12],[13],[14],[15],[16],[17],[18],[19],[20]

,[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]

)) AS  pvt

union all

select ItemCode,t1.ItmsGrpNam  ,0’1′,0’2′,0’3′,0’4′,0’5′,0’6′,0’7′,0’8′,

0’9′,0’10’,0’11’,0’12’,0’13’,0’14’,0’15’,0’16’,0’17’,0’18’,0’19’,

0’20’,0’21’,0’22’,0’23’,0’24’,0’25’,0’26’,0’27’,0’28’,0’29’,0’30’,0’31’

   from  OITM t0 left outer join OITB t1 on t0.ItmsGrpCod=t1.ItmsGrpCod   where t0.ItemCode not in (select ItemCode from  OIVL t0 )  .

Assigned tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.