Query for date wise closing balance

[Former Member]

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