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