Opening and Closing Balance Sum

Question

I have this table and sample data and I want to calculate the opening and closing balance. I want it to be in 6 column

i.e CreditOpening, DebitOpening, Credit, Debit, CreditClosing, DebitClosing

Here is the table structure and sample data DROP TABLE Transactions;

CREATE TABLE Transactions
(
ID INT, 
COATitle VARCHAR(35),
ConfigurationCode INT, 
DebitAmount NUMERIC, 
CreditAmount NUMERIC,
TransactionDate Date
)

INSERT INTO Transactions VALUES (1, 'Sales', 24, '2400', NULL, '2018-08-24');
INSERT INTO Transactions VALUES (2, 'Items', 24, NULL, '1200', '2018-08-24');
INSERT INTO Transactions VALUES (3, 'Bank', 24, NULL, '1200', '2018-08-24');
INSERT INTO Transactions VALUES (4, 'Meezan', 24, '1500', NULL, '2018-08-25');
INSERT INTO Transactions VALUES (5, 'Items', 24, NULL, '1500', '2018-08-25');
INSERT INTO Transactions VALUES (6, 'Bank', 24, NULL, '1200', '2018-08-26');
INSERT INTO Transactions VALUES (7, 'Sales', 24, '5400', NULL, '2018-08-26');
INSERT INTO Transactions VALUES (8, 'Items', 24, NULL, '1200', '2018-08-26');
INSERT INTO Transactions VALUES (9, 'Bank', 24, NULL, '3000', '2018-08-26');

I have this query and it's output as below:

;WITH CTE AS (
    SELECT *
    FROM 
    Transactions

)
SELECT 
COATitle, SUM([D].[DebitAmount]) DrAmount, SUM([D].[CreditAmount]) CrAmount

FROM(
    SELECT *,
            SUM(ISNULL(NULLIF(DebitAmount, 0), 0)-ISNULL(NULLIF(CreditAmount, 0), 0)) OVER (PARTITION BY CONFIGURATIONCODE ORDER BY ID) as Amount
    FROM CTE
    WHERE [TransactionDate] BETWEEN CAST('Aug 25 2018 11:21AM' AS DATE) AND CAST('Aug 25 2018 11:21AM' AS DATE)

    )D

    GROUP BY COATitle

OutPut :

COATitle    DrAmount    CrAmount
Items       NULL        1500
Meezan      1500        NULL

Now the data should look like this

COATitle    OpeningDebit  OpeningDebit  DrAmount  CrAmount  ClosingDebit  ClosingCredit  
Bank        0             0             NULL      1200      0             1200
Items       0             0             NULL      1200      0             1200
Sales       0             0             2400      NULL      2400             0

But once I run the query between dates 25 and 26 the result should be something like this

COATitle    OpeningDebit  OpeningCredit  DrAmount  CrAmount  ClosingDebit  ClosingCredit  
Bank        0             1200           NULL     4200      0             5400
Items       0             1200           NULL     2700      0             3900
Sales       0             0              5400     NULL      7800             0
Meezan      0             0              1500     NULL      1500             0

Meezan will not have opening balance as there was no in previous date. Now in case if there is Debit amount is given for any COATitle which was given Credit in previous dates it will subtract Credit From Debit.

Answer 1

You don't need the CTE in this case. It is redundant. You don't need to convert the date in string to date data type. Just specify the date in ISO format YYYY-MM-DD will do.

I am may be wrong but your expected data does not matches with the sample data.

Note : i excluded the ConfigurationCode in the query as i am not sure how that column play a part in your requirement.

DECLARE @Date_Fr    DATE    = '2018-08-25',
        @Date_To    DATE    = '2018-08-25'

SELECT  t.COATitle, 
        OpeningDebit    = ISNULL(o.OpeningDebit, 0), 
        OpeningCredit   = ISNULL(o.OpeningCredit, 0),
        t.DrAmount, t.CrAmount,
        ClosingDebit    = ISNULL(o.OpeningDebit, 0) + t.DrAmount,
        ClosingCredit   = ISNULL(o.OpeningCredit, 0) + t.CrAmount
FROM    (
            SELECT  COATitle, 
                    DrAmount    = SUM(CASE WHEN [TransactionDate] >= @Date_Fr THEN DebitAmount ELSE 0 END), 
                    CrAmount    = SUM(CASE WHEN [TransactionDate] >= @Date_Fr THEN CreditAmount ELSE 0 END)
            FROM    Transactions t
            WHERE   [TransactionDate]       <= @Date_To
            GROUP BY COATitle
        ) t
        OUTER APPLY
        (
            SELECT  OpeningDebit    = SUM(DebitAmount), OpeningCredit = SUM(CreditAmount)
            FROM    Transactions x
            WHERE   x.COATitle  = t.COATitle
            AND     x.[TransactionDate] < @Date_Fr
        ) o
WHERE   o.OpeningDebit  IS NOT NULL
OR      o.OpeningCredit iS NOT NULL