Need Ledger query modify with opening

Hi Prasad Raveendran

   Your last provided query is not giving correct output of Balance as you may see in image,i highlight in red,which is correct ouput of Balance

Secondly ,if i remove opening date from below line

INSERT INTO #tbl_Account_L_Four VALUES(1222,'Cheque In Hand',5000,'2021-01-18')

then  query is not getting data on date filter

I fined tuned the query. and please use this updated script. 

Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name varchar(50),Opening_Value decimal(10,2),Opening_Date date)
    Create table #tbl_trans_type (Trans_Type_ID int,trans_type_name varchar(50))
    Create table #tbl_transection (Trans_ID int,Level_Four_ID_C int,Level_Four_ID_D int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int,sell_ID int)
    INSERT INTO #tbl_Account_L_Four VALUES(1231,'Abdul Rauf',null,null)
    INSERT INTO #tbl_Account_L_Four VALUES(1222,'Cheque In Hand',5000,'2021-01-18')
    INSERT INTO #tbl_Account_L_Four VALUES(1215,'MBL 833968',null,null)


    insert into #tbl_trans_type VALUES(1,'Online')
    insert into #tbl_trans_type VALUES(2,'Cheque')
    insert into #tbl_trans_type VALUES(3,'Deposite')
    insert into #tbl_trans_type VALUES(4,'Tranfer')
    insert into #tbl_trans_type VALUES(5,'Return')

    INSERT INTO #tbl_transection VALUES(1,1231,1222,50000,'2021-01-18',2,null)
    INSERT INTO #tbl_transection VALUES(2,1231,1222,50000,'2021-01-18',2,null)
    INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3,null)
    INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5,null)
    INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2021-01-19',2,null)
	 INSERT INTO #tbl_transection VALUES(5,1231,1222,500,'2021-01-20',2,null)

	 select * from #tbl_Account_L_Four

	 select * from #tbl_trans_type

	 select * from #tbl_transection
DECLARE @startDate DATE = '2021-01-12';
DECLARE @EndDate DATE = '2021-01-20';
DECLARE @Level_Four_ID INT = 1222;

WITH transactions AS (
    SELECT
        Trans_ID = 0,
        Trans_Type = NULL,
        TransDate = NULL,
        Trans_Remarks = 'Opening',
        Code = NULL,
        Head = NULL,
        Debit = COALESCE(coa.Opening_Value, 0.00),
        Credit = 0.00
    FROM #tbl_Account_L_Four coa
    WHERE coa.Level_Four_ID = @Level_Four_ID
    UNION ALL
    SELECT
        t.Trans_ID,
        Trans_Type = ty.trans_type_name,
        TransDate = CONVERT(CHAR(10), t.Trans_Date, 101),
        Trans_Remarks = 
            CASE
                WHEN (coa.Opening_Date IS NOT NULL AND t.Trans_Date <= coa.Opening_Date) THEN
                    'Opening'
                WHEN (t.Trans_Type_ID = 2) THEN
                    CONCAT('Rs.', CONVERT(VARCHAR(50), CAST(t.Trans_Amount AS MONEY), 1), ' ', coac.Level_Four_Name)
                WHEN (t.Trans_Type_ID = 7 AND coac.Level_Four_ID = 410101) THEN
                    CONCAT('Cash Received From Walking, Invoice No ', T.Sell_ID, ', Amount = ', T.Trans_Amount)
                WHEN (t.Trans_Type_ID = 7 AND coac.Level_Four_ID != 410101) THEN
                    CONCAT('Cash Received From ', coac.Level_Four_Name, ', Invoice No ', T.Sell_ID, ', Amount = ', T.Trans_Amount)
                WHEN (t.Trans_Type_ID = 8) THEN
                    CONCAT('Sales To ', coa.Level_Four_Name, ', Invoice No ', T.Sell_ID, ', Amount = ', T.Trans_Amount)
                WHEN (t.Trans_Type_ID = 2) THEN
                    CONCAT(COA.Level_Four_Name, ' From ', Coac.Level_Four_Name, '', T.Trans_Amount)
            END,
        Code = IIF(t.Level_Four_ID_C = @Level_Four_ID, t.Level_Four_ID_D, t.Level_Four_ID_C),
        Head = IIF(t.Level_Four_ID_C = @Level_Four_ID, coa.Level_Four_Name, coac.Level_Four_Name),
        Debit = IIF(t.Level_Four_ID_D = @Level_Four_ID, t.Trans_Amount, 0.00),
        Credit = IIF(t.Level_Four_ID_C = @Level_Four_ID, t.Trans_Amount, 0.00)
    FROM #tbl_transection t
    INNER JOIN #tbl_trans_type ty ON ty.trans_type_ID = t.Trans_Type_ID
    LEFT JOIN #tbl_Account_L_Four coa ON coa.Level_Four_ID = t.Level_Four_ID_D
    LEFT JOIN #tbl_Account_L_Four coac ON coac.Level_Four_ID = t.Level_Four_ID_C
    WHERE @Level_Four_ID IN (t.Level_Four_ID_C, t.Level_Four_ID_D)
    AND t.Trans_Date <= @EndDate
)
, cte2 AS (
    SELECT
        tn.Trans_ID,
        tn.Trans_Type,
        tn.TransDate,
        tn.Trans_Remarks,
        tn.Code,
        tn.Head,
        tn.Debit,
        tn.Credit,
        Balance = SUM(tn.Debit - tn.Credit) OVER (ORDER BY tn.Trans_Id)
    FROM transactions tn
    UNION ALL
    SELECT
        Trans_ID = 9999,
        Trans_Type = NULL,
        Trans_Date = NULL,
        Trans_Remarks = 'Total',
        Code = NULL,
        Head = NULL,
        Debit = SUM(tn.Debit),
        Credit = SUM(tn.Credit),
        Balance = SUM(tn.Debit) - SUM(tn.Credit)
    FROM transactions tn
)

-- Select the specific rows
SELECT
    Trans_ID,
    Trans_Type,
    TransDate,
    Trans_Remarks,
    Code,
    Head,
    Debit,
    Credit,
    Balance
FROM cte2
WHERE Trans_ID = 0 OR Trans_ID = 5 OR Trans_ID = 9999
ORDER BY CASE
    WHEN Trans_ID = 0 THEN '01/01/1900'
    WHEN Trans_ID = 9999 THEN '12/31/9999'
    ELSE TransDate
END, Trans_ID;



DROP TABLE   #tbl_Account_L_Four;
DROP TABLE   #tbl_trans_type;
DROP TABLE   #tbl_transection;

please use this updated script 

DECLARE @startDate DATE = '2021-01-12';
DECLARE @EndDate DATE = '2021-01-20';
DECLARE @Level_Four_ID INT = 1222;

WITH initialtransactions AS (
    SELECT
        Trans_ID = 0,
        Trans_Type = NULL,
        TransDate = NULL,
        Trans_Remarks = 'Opening',
        Code = NULL,
        Head = NULL,
        Debit = IIF(coa.Opening_Value > 0, coa.Opening_Value, 0.00),
        Credit = IIF(coa.Opening_Value < 0, -coa.Opening_Value, 0.00)
    FROM #tbl_Account_L_Four coa
    WHERE coa.Level_Four_ID = @Level_Four_ID
    UNION ALL
    SELECT
        t.Trans_ID,
        Trans_Type = ty.trans_type_name,
        TransDate = CONVERT(CHAR(10), t.Trans_Date, 101),
        Trans_Remarks = 
            CASE
                WHEN (coa.Opening_Date IS NOT NULL AND t.Trans_Date <= coa.Opening_Date) THEN
                    'Opening'
                WHEN (t.Trans_Type_ID = 2) THEN
                    CONCAT('Rs.', CONVERT(VARCHAR(50), CAST(t.Trans_Amount AS MONEY), 1), ' ', coac.Level_Four_Name)
                WHEN (t.Trans_Type_ID = 7 AND coac.Level_Four_ID = 410101) THEN
                    CONCAT('Cash Received From Walking, Invoice No ', T.Sell_ID, ', Amount = ', T.Trans_Amount)
                WHEN (t.Trans_Type_ID = 7 AND coac.Level_Four_ID != 410101) THEN
                    CONCAT('Cash Received From ', coac.Level_Four_Name, ', Invoice No ', T.Sell_ID, ', Amount = ', T.Trans_Amount)
                WHEN (t.Trans_Type_ID = 8) THEN
                    CONCAT('Sales To ', coa.Level_Four_Name, ', Invoice No ', T.Sell_ID, ', Amount = ', T.Trans_Amount)
                WHEN (t.Trans_Type_ID = 2) THEN
                    CONCAT(COA.Level_Four_Name, ' From ', Coac.Level_Four_Name, '', T.Trans_Amount)
            END,
        Code = IIF(t.Level_Four_ID_C = @Level_Four_ID, t.Level_Four_ID_D, t.Level_Four_ID_C),
        Head = IIF(t.Level_Four_ID_C = @Level_Four_ID, coa.Level_Four_Name, coac.Level_Four_Name),
        Debit = IIF(t.Level_Four_ID_D = @Level_Four_ID, t.Trans_Amount, 0.00),
        Credit = IIF(t.Level_Four_ID_C = @Level_Four_ID, t.Trans_Amount, 0.00)
    FROM #tbl_transection t
    INNER JOIN #tbl_trans_type ty ON ty.trans_type_ID = t.Trans_Type_ID
    LEFT JOIN #tbl_Account_L_Four coa ON coa.Level_Four_ID = t.Level_Four_ID_D
    LEFT JOIN #tbl_Account_L_Four coac ON coac.Level_Four_ID = t.Level_Four_ID_C
    WHERE @Level_Four_ID IN (t.Level_Four_ID_C, t.Level_Four_ID_D)
    AND t.Trans_Date <= @EndDate
)
, transactions AS (
    -- Get opening balance
    SELECT
        Trans_ID = 0,
        Trans_Type = NULL,
        TransDate = NULL,
        Trans_Remarks = 'Opening',
        Code = NULL,
        Head = NULL,
        Debit = IIF(coa.Opening_Value > 0, coa.Opening_Value, 0.00),
        Credit = IIF(coa.Opening_Value < 0, -coa.Opening_Value, 0.00)
    FROM #tbl_Account_L_Four coa
    WHERE coa.Level_Four_ID = @Level_Four_ID
    UNION ALL
    SELECT
        tn.Trans_ID,
        tn.Trans_Type,
        tn.TransDate,
        tn.Trans_Remarks,
        tn.Code,
        tn.Head,
        tn.Debit,
        tn.Credit
    FROM initialtransactions tn
    WHERE (tn.TransDate IS NULL OR tn.TransDate BETWEEN @startDate AND @EndDate)
)
, cte2 AS (
    SELECT
        tn.Trans_ID,
        tn.Trans_Type,
        tn.TransDate,
        tn.Trans_Remarks,
        tn.Code,
        tn.Head,
        CASE WHEN Trans_ID = 0 THEN NULL ELSE tn.Debit END AS Debit,
        CASE WHEN Trans_ID = 0 THEN NULL ELSE tn.Credit END AS Credit,
        Balance = SUM(tn.Debit - tn.Credit) OVER (ORDER BY tn.Trans_Id)
    FROM transactions tn
    UNION ALL
    SELECT
        Trans_ID = 9999,
        Trans_Type = NULL,
        Trans_Date = NULL,
        Trans_Remarks = 'Total',
        Code = NULL,
        Head = NULL,
        Debit = SUM(tn.Debit),
        Credit = SUM(tn.Credit),
        Balance = SUM(tn.Debit) - SUM(tn.Credit)
    FROM transactions tn
)

-- Select the specific rows
SELECT
    Trans_ID,
    Trans_Type,
    TransDate,
    Trans_Remarks,
    Code,
    Head,
    Debit,
    Credit,
    Balance
FROM cte2
WHERE Trans_ID = 0 OR Trans_ID = 5 OR Trans_ID = 9999
ORDER BY CASE
    WHEN Trans_ID = 0 THEN '01/01/1900'
    WHEN Trans_ID = 9999 THEN '12/31/9999'
    ELSE TransDate
END, Trans_ID;

You want to modify the query to consider the Opening_Value and Opening_Date from #tbl_Account_L_Four if they exist. If Opening_Date exists, then data from #tbl_Account_L_Four should be used until that date, and afterward, data from #tbl_transection should be calculated.

To achieve this, you can update the initaltransactions CTE to handle these conditions. Here's the modified query:

DECLARE @startDate DATE = '2021-01-12';
DECLARE @EndDate DATE = '2021-01-20';
DECLARE @Level_Four_ID INT = 1222;

WITH initialtransactions AS (
    SELECT
        t.Trans_ID,
        Trans_Type = ty.trans_type_name,
        TransDate = CONVERT(CHAR(10), t.Trans_Date, 101),
        Trans_Remarks = 
            CASE
                WHEN (coa.Opening_Date IS NOT NULL AND t.Trans_Date <= coa.Opening_Date) THEN
                    'Opening'
                WHEN (t.Trans_Type_ID = 2) THEN
                    CONCAT('Rs.', CONVERT(VARCHAR(50), CAST(t.Trans_Amount AS MONEY), 1), ' ', coac.Level_Four_Name)
                WHEN (t.Trans_Type_ID = 7 AND coac.Level_Four_ID = 410101) THEN
                    CONCAT('Cash Received From Walking, Invoice No ', T.Sell_ID, ', Amount = ', T.Trans_Amount)
                WHEN (t.Trans_Type_ID = 7 AND coac.Level_Four_ID != 410101) THEN
                    CONCAT('Cash Received From ', coac.Level_Four_Name, ', Invoice No ', T.Sell_ID, ', Amount = ', T.Trans_Amount)
                WHEN (t.Trans_Type_ID = 8) THEN
                    CONCAT('Sales To ', coa.Level_Four_Name, ', Invoice No ', T.Sell_ID, ', Amount = ', T.Trans_Amount)
                WHEN (t.Trans_Type_ID = 2) THEN
                    CONCAT(COA.Level_Four_Name, ' From ', Coac.Level_Four_Name, '', T.Trans_Amount)
            END,
        Code = IIF(t.Level_Four_ID_C = @Level_Four_ID, t.Level_Four_ID_D, t.Level_Four_ID_C),
        Head = IIF(t.Level_Four_ID_C = @Level_Four_ID, coa.Level_Four_Name, coac.Level_Four_Name),
        Debit = IIF(t.Level_Four_ID_D = @Level_Four_ID, t.Trans_Amount, 0.00),
        Credit = IIF(t.Level_Four_ID_C = @Level_Four_ID, t.Trans_Amount, 0.00)
    FROM #tbl_transection t
    INNER JOIN #tbl_trans_type ty ON ty.trans_type_ID = t.Trans_Type_ID
    LEFT JOIN #tbl_Account_L_Four coa ON coa.Level_Four_ID = t.Level_Four_ID_D
    LEFT JOIN #tbl_Account_L_Four coac ON coac.Level_Four_ID = t.Level_Four_ID_C
    LEFT JOIN #tbl_Account_L_Four coao ON coao.Level_Four_ID = @Level_Four_ID
    WHERE @Level_Four_ID IN (t.Level_Four_ID_C, t.Level_Four_ID_D)
    AND t.Trans_Date <= @EndDate
)
, transactions AS (
    -- Get opening balance
    SELECT
        Trans_ID = 0,
        Trans_Type = NULL,
        TransDate = NULL,
        Trans_Remarks = 'Opening',
        Code = NULL,
        Head = NULL,
        Debit = IIF(coao.Opening_Value > 0, coao.Opening_Value, 0.00),
        Credit = IIF(coao.Opening_Value < 0, -coao.Opening_Value, 0.00)
    FROM #tbl_Account_L_Four coao
    WHERE coao.Level_Four_ID = @Level_Four_ID
    UNION ALL
    SELECT
        tn.Trans_ID,
        tn.Trans_Type,
        tn.TransDate,
        tn.Trans_Remarks,
        tn.Code,
        tn.Head,
        tn.Debit,
        tn.Credit
    FROM initialtransactions tn
    WHERE (tn.TransDate IS NULL OR tn.TransDate BETWEEN @startDate AND @EndDate)
)

-- The rest of your query remains unchanged

In this modified query, the initialtransactions CTE first checks if Opening_Date exists in #tbl_Account_L_Four. If it does, it uses the "Opening" transaction for the specified date range. If Opening_Date is not present, it proceeds with the transaction data from #tbl_transection as before.

This way, the query correctly handles the scenario where Opening_Date exists and uses the data from #tbl_Account_L_Four until that date and transitions to transaction data afterward.