Sql-server – calculate running balance of debit credit using union all

sql serversql-server-2008sql-server-2008-r2

i'm making account statement for which i need that account balance before today and with each transaction row of debit or credit plus and minus from initial opening balance on the basis debit/credit amount rules….
for this i'm using this approach 

SELECT '1'AS VoucherNo,''AS DocumentDate,'Opening' AS Description,isnull(SUM(JournalDetials.Debit),0)AS Debit,
    isnull(SUM(JournalDetials.Credit),0)AS Credit,(isnull(SUM(JournalDetials.Debit),0)-isnull(SUM(JournalDetials.Credit),0))[Balance],
    ''as JournalTransationStatus
    FROM JournalDetials INNER JOIN JournalMains ON JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId=GLMainAccount.Id
    where(JournalDetials.AccountId='001653')AND(JournalDetials.Date<'03/31/2019')

    union all

    select(JournalMains.VoucherNumberPrefix+JournalMains.VoucherNumber)AS VoucherNo,JournalDetials.Date as DocumentDate,
    isnull(JournalDetials.Description,''),isnull(JournalDetials.Debit,0),isnull(JournalDetials.Credit,0),''[Balance],
    ''JournalTransationStatus
    from JournalDetials inner join JournalMains on JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId = GLMainAccount.Id
where(JournalDetials.AccountId='001653' and JournalDetials.Date BETWEEN '03/31/2019' AND '04/09/2020')

enter image description here

I was trying to apply this solution but couldn't able to run this in SQL 2008 due to this. so now, how to calculate running balance total along with each debit credit value in SQL 2008?

Best Answer

Try this (you may need to adjust data types or play with clustered index on temporary table, may be use identity column):

CREATE TABLE #x(
VoucherNo int, 
DocumentDate datetime, 
Description varchar(255), 
Debit numeric(18,2), 
Credit numeric(18,2),
Balance numeric(18,2)
);
CREATE UNIQUE CLUSTERED INDEX cx_x ON #x(DocumentDate, VoucherNo);

INSERT INTO #x (VoucherNo, DocumentDate, Description, Debit, Credit)
SELECT '1'AS VoucherNo,''AS DocumentDate,'Opening' AS Description,isnull(SUM(JournalDetials.Debit),0)AS Debit,
    isnull(SUM(JournalDetials.Credit),0)AS Credit
    FROM JournalDetials INNER JOIN JournalMains ON JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId=GLMainAccount.Id
    where(JournalDetials.AccountId='001653')AND(JournalDetials.Date<'03/31/2019')

    union all

    select(JournalMains.VoucherNumberPrefix+JournalMains.VoucherNumber)AS VoucherNo,JournalDetials.Date as DocumentDate,
    isnull(JournalDetials.Description,''),isnull(JournalDetials.Debit,0),isnull(JournalDetials.Credit,0)
    from JournalDetials inner join JournalMains on JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId = GLMainAccount.Id
where(JournalDetials.AccountId='001653' and JournalDetials.Date BETWEEN '03/31/2019' AND '04/09/2020')

DECLARE @Balance numeric(18,2), @DocumentDate datetime, @amt numeric(18,2), @VoucherNo int;
SET @Balance = 0;

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
  FOR SELECT DocumentDate, VoucherNo, Debit - Credit FROM #x ORDER BY DocumentDate, VoucherNo;

OPEN c;

FETCH c INTO @DocumentDate, @VoucherNo, @amt;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @Balance = @Balance + @amt;
  UPDATE #x SET Balance = @Balance WHERE DocumentDate = @DocumentDate AND VoucherNo = @VoucherNo;
  FETCH c INTO @DocumentDate, @VoucherNo, @amt;
END

CLOSE c; DEALLOCATE c;

SELECT VoucherNo, DocumentDate, Description, Debit, Credit, Balance, '' AS JournalTransationStatus
  FROM #x 
  ORDER BY DocumentDate, VoucherNo;

DROP TABLE #x;
Related Question