'SQLSERVER Running Balance

Actually i have problem on my query to get the running balance , i have debit and credit transaction i need one column to showing the cumulative running balance this is the code i used :

Select * From ( 

Select  D.AccNo, H.[Date], A.AccountName, H.TrxNo,     
           (Case When ((D.Remark = '') or (D.Remark is Null)) Then H.Trxnote Else D.Remark End) As TrxDetailDescA, 
           (D.Debit * 1) AS DebitValue, (D.Credit * 1) AS CreditValue,SUM(COALESCE(D.Debit, 0) - COALESCE(D.Credit, 0)) AS Balance
From   TblHeadTrans H, TblTransDetails D, TblAccount A
Where      H.Guid = D.[LineNo]
      And D.AccNo = A.AccountNo 
         
      And H.[Date] >= '01-01-2022'  And H.[Date] <= '10-07-2022'      And D.AccNo >= '1003' 
group by AccNo,H.[Date],A.AccountName,H.TrxNo,D.Remark,h.Trxnote,d.Debit,d.Credit
Union All 

Select  D.AccNo, Null As TrxDate, A.AccountName, Null As TrxNo, 
  
       'Opening Balance' As TrxDetailDesc,
        
       Case When (Sum(D.Debit  * 1) - Sum(D.Credit *1)) < 0 then 0 
       Else (Sum(D.Debit * 1) - Sum(D.Credit * 1)) End As DebitValue,  
       Case When (Sum(D.Credit * 1) - Sum(D.Debit  * 1)) < 0 then 0 
       Else (Sum(D.Credit * 1) - Sum(D.Debit * 1)) End As CreditValue
      ,   SUM(COALESCE(d.Debit, 0) - COALESCE(d.credit, 0)) AS Balance
From   TblHeadTrans H, TblTransDetails D, TblAccount A 
Where       H.guid = D.[LineNo]   And D.AccNo = A.AccountNo 
        And d.[Date] < '01-01-2022'      And D.accno = '1003'  
Group By D.AccNo, A.AccountName,H.Date,H.TrxNo

) ReportData 
 WHERE 1=1     
Order By AccNo, [Date], TrxNo 

and the result showing as the picture: the result



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source