(

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(1,1231,1222,50000,'2023-10-18',2,null)

declare @start as date = '2023-10-19'

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,

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,

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

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

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