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

Here's a possible solution for you. First, sample data:

declare @Transactions table
(
    ID int, 
    COATitle varchar(35),
    ConfigurationCode int, 
    DebitAmount money, 
    CreditAmount money,
    TransactionDate date
);

insert @Transactions values
    (1, 'Sales', 24, 2400, NULL, '20180824'),
    (2, 'Items', 24, NULL, 1200, '20180824'),
    (3, 'Bank', 24, NULL, 1200, '20180824'),
    (4, 'Meezan', 24, 1500, NULL, '20180825'),
    (5, 'Items', 24, NULL, 1500, '20180825'),
    (6, 'Bank', 24, NULL, 1200, '20180826'),
    (7, 'Sales', 24, 5400, NULL, '20180826'),
    (8, 'Items', 24, NULL, 1200, '20180826'),
    (9, 'Bank', 24, NULL, 3000, '20180826');

A couple of things to note here. There's no need to use quotes to delimit numeric literals as you've done in the original question, and I would generally advise against using the numeric data type without specifying the precision and scale explicitly. I've chosen the money type for debit and credit amounts instead.

Next, I'll use a couple of local variables to control the operation of the query. For your first test case we'll use:

declare @BeginDate date = '20180801';
declare @EndDate date = '20180824';

Here's the implementation:

with RawDataCTE as
(
    select
        T.COATitle,
        OpeningDebit = coalesce(sum(case when T.TransactionDate < @BeginDate then T.DebitAmount end), 0),
        OpeningCredit = coalesce(sum(case when T.TransactionDate < @BeginDate then T.CreditAmount end), 0),
        DrAmount = sum(case when T.TransactionDate between @BeginDate and @EndDate then T.DebitAmount end),
        CrAmount = sum(case when T.TransactionDate between @BeginDate and @EndDate then T.CreditAmount end)
    from
        @Transactions T
    group by
        T.COATitle
)
select
    R.COATitle,
    R.OpeningDebit,
    R.OpeningCredit,
    R.DrAmount,
    R.CrAmount,
    ClosingDebit = R.OpeningDebit + coalesce(R.DrAmount, 0),
    ClosingCredit = R.OpeningCredit + coalesce(R.CrAmount, 0)
from
    RawDataCTE R
where
    R.OpeningDebit > 0 or
    R.OpeningCredit > 0 or
    R.DrAmount > 0 or
    R.CrAmount > 0;

The CTE groups everything by COATitle, and as I've assumed from your desired results, produces non-null results for the opening balances but may produce null results for the sum of debits and credits that fall within the desired time frame. The CTE doesn't try to decide which COATitle records should be included or excluded because we need to have the data aggregated before making that decision.

The query outside the CTE builds the closing balances from the CTE data and omits any COATitle for which there are no opening balances and no entries made during the time period (and therefore no closing balances). When I run the query for 2018-08-01 through 2018-08-24, the result is:

And for 2018-08-25 through 2018-08-26, the result set is:

Technically you could do this without a CTE if you wanted; you'd just have to use a HAVING clause instead of a WHERE to determine which accounts are to be included, and you'd have to define ClosingDebit and ClosingCredit independently rather than as sums of the other fields in the result set. For instance, this will work:

select
    T.COATitle,
    OpeningDebit = coalesce(sum(case when T.TransactionDate < @BeginDate then T.DebitAmount end), 0),
    OpeningCredit = coalesce(sum(case when T.TransactionDate < @BeginDate then T.CreditAmount end), 0),
    DrAmount = sum(case when T.TransactionDate between @BeginDate and @EndDate then T.DebitAmount end),
    CrAmount = sum(case when T.TransactionDate between @BeginDate and @EndDate then T.CreditAmount end),
    ClosingDebit = coalesce(sum(case when T.TransactionDate <= @EndDate then T.DebitAmount end), 0),
    ClosingCredit = coalesce(sum(case when T.TransactionDate <= @EndDate then T.CreditAmount end), 0)
from
    @Transactions T
group by
    T.COATitle
having
    sum(case when T.TransactionDate <= @EndDate and (T.DebitAmount > 0 or T.CreditAmount > 0) then 1 else 0 end) > 0;

I find the CTE version a little easier to read and understand, but your mileage may vary.

Answer 2

This may help. I put additional check, because I don't know if situation when opening balance is debit and closing is credit for example, is valid.

DECLARE 
    @from date = '25-08-2018',
    @to date = '26-08-2018'
;

WITH Items (COATitle) AS (
    SELECT DISTINCT COATitle
    FROM dbo.[Transactions]
), OpeningBalance (COATitle, OpeningAmount) AS (
    SELECT COATitle, SUM(ISNULL(DebitAmount, 0)) - SUM(ISNULL(CreditAmount, 0))
    FROM dbo.[Transactions]
    WHERE TransactionDate < @from
    GROUP BY COATitle
), DebitCredit(COATitle, DebitAmount, CreditAmount)  AS (
    SELECT COATitle, SUM(ISNULL(DebitAmount, 0)), SUM(ISNULL(CreditAmount, 0))
    FROM dbo.[Transactions]
    WHERE (@from <= TransactionDate) AND (TransactionDate <= @to)
    GROUP BY COATitle
)
SELECT 
    i.COATitle,
    OpeningDebitAmount = (CASE WHEN SUM(ISNULL(ob.OpeningAmount, 0)) < 0 THEN 0 ELSE SUM(ISNULL(ob.OpeningAmount, 0)) END),
    OpeningCreditAmount = (CASE WHEN SUM(ISNULL(ob.OpeningAmount, 0)) < 0 THEN -SUM(ISNULL(ob.OpeningAmount, 0)) ELSE 0 END),
    DebitAmount = SUM(ISNULL(dc.DebitAmount, 0)), 
    CreditAmount = SUM(ISNULL(dc.CreditAmount, 0)),
    ClosingDebitAmount = (CASE WHEN SUM(ISNULL(ob.OpeningAmount,0)+ISNULL(dc.DebitAmount,0)-ISNULL(dc.CreditAmount, 0)) < 0 THEN 0 ELSE SUM(ISNULL(ob.OpeningAmount,0)+ISNULL(dc.DebitAmount,0)-ISNULL(dc.CreditAmount, 0)) END),
    ClosingCreditAmount = (CASE WHEN SUM(ISNULL(ob.OpeningAmount,0)+ISNULL(dc.DebitAmount,0)-ISNULL(dc.CreditAmount, 0)) < 0 THEN -SUM(ISNULL(ob.OpeningAmount,0)+ISNULL(dc.DebitAmount,0)-ISNULL(dc.CreditAmount, 0)) ELSE 0 END)
FROM Items i
LEFT JOIN OpeningBalance ob ON (i.COATitle = ob.COATitle)
LEFT JOIN DebitCredit dc ON (i.COATitle = dc.COATitle)
GROUP BY i.COATitle

Answer 3

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