Calculate trial balance using opening and closing balance in SQL Server

Hi ishuhasan21,

Refer below query sample.

SQL

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, 900, 1200, '20180824'),
    (4, 'Meezan', 24, 1500, NULL, '20180824'),
    (5, 'Items', 24, NULL, 1500, '20180825'),
    (6, 'Bank', 24, 1000, 1200, '20180829'),
    (7, 'Sales', 24, 5400, 500, '20180826'),
    (8, 'Items', 24, NULL, 1200, '20180829'),
    (9, 'Bank', 24, NULL, 3000, '20180822'),
	(10, 'Sales', 24, 2400, 100, '20180826'),
    (11, 'Items', 24, NULL, 1200, '20180828'),
    (12, 'Bank', 24, 300, 1200, '20180827'),
    (13, 'Meezan', 24, 1500, NULL, '20180827'),
    (14, 'Items', 24, NULL, 1500, '20180825'),
    (15, 'Bank', 24, NULL, 1200, '20180826'),
    (16, 'Sales', 24, 5400, 500, '20180827'),
    (17, 'Items', 24, NULL, 1200, '20180825'),
    (18, 'Bank', 24, NULL, 3000, '20180822');

DECLARE @BeginDate DATE = '20180825';
DECLARE @EndDate DATE = '20180829';

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 = COALESCE(SUM(CASE WHEN T.TransactionDate BETWEEN @BeginDate AND @EndDate then T.DebitAmount END),0),
        CrAmount = COALESCE(SUM(CASE WHEN T.TransactionDate BETWEEN @BeginDate AND @EndDate then T.CreditAmount END),0)
    FROM
        @Transactions T
    GROUP BY
        T.COATitle
)
SELECT
    R.COATitle,
    OpeningDebit2 = COALESCE((CASE WHEN (R.OpeningDebit - R.OpeningCredit) > 0 THEN(R.OpeningDebit - R.OpeningCredit) END), 0) ,
    OpeningCredit2 =COALESCE((CASE WHEN (R.OpeningCredit - R.OpeningDebit) > 0 THEN(R.OpeningCredit - R.OpeningDebit) END), 0),
    R.DrAmount,
    R.CrAmount,
	ClosingDebit = COALESCE((CASE WHEN (R.OpeningDebit + R.DrAmount - R.CrAmount - R.OpeningCredit) > 0 THEN(R.OpeningDebit + R.DrAmount - R.CrAmount - R.OpeningCredit) END), 0),
	ClosingCredit = COALESCE((CASE WHEN (R.OpeningCredit + R.CrAmount - R.DrAmount - R.OpeningDebit) > 0 THEN(R.OpeningCredit + R.CrAmount - R.DrAmount - R.OpeningDebit) END), 0)
FROM
    RawDataCTE R
WHERE
    R.OpeningDebit > 0 or
    R.OpeningCredit > 0 or
    R.DrAmount > 0 or
    R.CrAmount > 0;

Output