CODEWITHSUNDEEP
sqlsql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Get opening balance

I have this table along with sample data. I want to calculate the opening balance from the data present in the DB.

DROP TABLE VoucherPayment;
CREATE TABLE VoucherPayment
(
VoucherID Int,
VoucherName nvarchar(25),
Credit int,
Debit int,
VoucherDate date
);

INSERT INTO VoucherPayment VALUES (1, 'Cash Payment', '2000', NULL, '2017-12-09');
INSERT INTO VoucherPayment VALUES (2, 'Cash Payment', NULL, '1900', '2017-12-15');
INSERT INTO VoucherPayment VALUES (3, 'Cash Payment', '5680', NULL, '2017-12-19');
INSERT INTO VoucherPayment VALUES (4, 'Cash Receipt', '1200', NULL, '2017-12-20');

I have wrote a query which is providing output right to some extent mentioned below.

WITH CTE AS (
    SELECT 
    VoucherID, VoucherName, SUM(Debit) [DrAmount], SUM(Credit) [CrAmount]

    FROM 
    VoucherPayment

    WHERE 
    [VoucherDate] BETWEEN CAST('Dec  1 2017 12:00AM' AS DATE) AND CAST('Dec 30 2017 12:00AM' AS DATE)
    GROUP BY 
    VoucherName, VoucherID

)
SELECT *,
        SUM(ISNULL(DrAmount, 0)+ISNULL(CrAmount, 0)) OVER (ORDER BY VoucherID 
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Amount

FROM CTE

The output this query giving is 

VoucherID   VoucherName               DrAmount    CrAmount    Amount
----------- ------------------------- ----------- ----------- -----------
1           Cash Payment              NULL        2000        2000
2           Cash Payment              1900        NULL        3900
3           Cash Payment              NULL        5680        9580
4           Cash Receipt              NULL        1200        10780

If I change the date to Dec 19 I am getting this output

VoucherID   VoucherName               DrAmount    CrAmount    Amount
----------- ------------------------- ----------- ----------- -----------
3           Cash Payment              NULL        5680        5680
4           Cash Receipt              NULL        1200        6880

Instead I want it to be like this 

VoucherID   VoucherName      DrAmount    CrAmount    Amount    Opening
----------- ---------------- ----------- ----------- --------- ---------
3           Cash Payment     NULL        5680        9580      3900
4           Cash Receipt     NULL        1200        10780     9580

Upvotes: 4

Views: 124

Answers (1)

DineshDB
DineshDB

Reputation: 6193

Try this:

;WITH CTE AS (
    SELECT 
    VoucherID,VoucherDate, VoucherName, SUM(Debit) [DrAmount], SUM(Credit) [CrAmount]
   FROM VoucherPayment
   --WHERE [VoucherDate] BETWEEN CAST('Dec  01 2017 12:00AM' AS DATE) AND CAST('Dec 30 2017 12:00AM' AS DATE)
    GROUP BY VoucherName,VoucherDate, VoucherID
)
SELECT VoucherID, VoucherName,D.DrAmount,D.CrAmount,D.Amount,D.Amount-D.CrAmount [Opening]
FROM(
    SELECT *,
            SUM(ISNULL(DrAmount, 0)+ISNULL(CrAmount, 0)) OVER (ORDER BY VoucherID 
                     ) as Amount
    FROM CTE
    )D
WHERE [VoucherDate] BETWEEN CAST('Dec  19 2017 12:00AM' AS DATE) AND CAST('Dec 30 2017 12:00AM' AS DATE)

Output:

VoucherID   VoucherName     DrAmount    CrAmount    Amount  Opening
3           Cash Payment    NULL        5680        9580    3900
4           Cash Receipt    NULL        1200        10780   9580

Upvotes: 1

Related Questions