SQL Server 2022

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,'2023-10-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(0,1231,1222,500,'2023-10-17',2,null)
INSERT INTO #tbl_transection VALUES(1,1231,1222,50000,'2023-10-18',2,null)
INSERT INTO #tbl_transection VALUES(2,1231,1222,50000,'2023-10-18',2,null)
INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2023-10-18',3,null) 
INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2023-10-18',5,null)
INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2023-10-19',2,null)  
INSERT INTO #tbl_transection VALUES(6,1231,1222,500,'2023-10-20',2,null);

15 rows affected

declare @C_ID as int = 1222
declare @start as date = '2023-10-17'
declare @end as date = '2023-10-20';

with cte as
(
select C_ID = a.Level_Four_ID,
       Trans_ID = 0,
       Remarks = 'Opening',
       Debit   = null,
       Credit  = null,
      Trans_Date= Opening_date,
       Balance = a.Opening_Value
from   #tbl_Account_L_Four a
where  a.Level_Four_ID = @C_ID

union all
  
select C_ID = a.Level_Four_ID,
       t.Trans_ID,
       Remarks = '',
       Debit   = case when t.Trans_Amount > 0 then +Trans_Amount else 0 end,
       Credit  = case when t.Trans_Amount < 0 then -Trans_Amount else 0 end,
      Trans_Date,
       Balance = t.Trans_Amount
from   #tbl_Account_L_Four a
       inner join 
       (
           select v.Level_Four_ID, t.Trans_ID, t.Trans_date, v.Trans_Amount
           from   #tbl_transection t 
                  cross apply
                  (
                      values
                      (Level_Four_ID_C, -Trans_Amount),
                      (Level_Four_ID_D, +Trans_Amount)
                  ) v (Level_Four_ID, Trans_Amount)
       ) t           on a.Level_Four_ID = t.Level_Four_ID
where  a.Level_Four_ID = @C_ID
and    t.Trans_date >= @start
and    t.Trans_date <= @end
)
select C_ID, Trans_ID, Remarks,Trans_Date Debit, Credit, 
       Balance = sum(Balance) over (partition by C_ID order by Trans_ID)
from   cte
order by C_ID, Trans_ID

C_ID	Trans_ID	Remarks	Debit	Credit	Balance
1222	0	Opening	2023-10-18	null	5500.00
1222	0		2023-10-17	0.00	5500.00
1222	1		2023-10-18	0.00	55500.00
1222	2		2023-10-18	0.00	105500.00
1222	3		2023-10-18	44444.00	61056.00
1222	4		2023-10-18	0.00	105500.00
1222	5		2023-10-19	44444.00	61056.00
1222	6		2023-10-20	0.00	61556.00

declare @C_ID as int = 1222
declare @start as date = '2023-10-19'
declare @end as date = '2023-10-20'

; WITH Unio AS (
SELECT NULL AS Trans_ID, Opening_Date AS E_Date,
IIF(Opening_value > 0, Opening_value, 0) AS Debit,
IIF(Opening_value < 0, Opening_value, 0) AS Credit,
Opening_value AS Amount
FROM #tbl_Account_L_Four
WHERE Level_Four_ID = @C_ID
UNION ALL
SELECT Trans_ID, Trans_date, IIF(Level_Four_ID_D = @C_ID, Trans_Amount, 0),
IIF(Level_Four_ID_C = @C_ID, Trans_Amount, 0),
CASE WHEN Level_Four_ID_D = @C_ID THEN Trans_Amount
WHEN Level_Four_ID_c = @C_ID THEN -1 * Trans_Amount
END
FROM #tbl_transection
WHERE Trans_date > (SELECT Opening_Date FROM #tbl_Account_L_Four WHERE Level_Four_ID = @C_ID) or Trans_date>'2023-10-01'
),
runsum AS (
SELECT Trans_ID, E_Date, Debit, Credit,
SUM(Amount) OVER(ORDER BY E_Date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS Balance
FROM Unio
),
prevbal AS (

select Top(1)BALANCE prevbal from runsum
where E_Date<@start
Order by E_Date desc,Trans_ID desc)
SELECT NULL AS ID, NULL AS E_Date, 'Opening' AS Remarks,
NULL AS Debit, NULL AS Credit, prevbal AS Balance
FROM prevbal

UNION ALL
SELECT Trans_ID, E_Date, '' AS Remarks, Debit, Credit, Balance
FROM runsum
WHERE E_Date BETWEEN @start AND @end
ORDER BY E_Date asc

ID	E_Date	Remarks	Debit	Credit	Balance
null	null	Opening	null	null	100500.00
5	2023-10-19		0.00	44444.00	61056.00
6	2023-10-20		500.00	0.00	61556.00