Hi @MelissaMa-MSFT
When i am using Order by Trans_ID
, then total is coming in First row ,which should not come.
Create table #tbl_COA (Level_Four_ID int,Level_Four_Name varchar(50),Opening decimal(10,2))
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)
INSERT INTO #tbl_COA VALUES(1231,'Abdul Rauf',60000)
INSERT INTO #tbl_COA VALUES(1222,'Cheque In Hand',45000)
INSERT INTO #tbl_COA VALUES(1215,'MBL 833968',0)
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(4,'Return')
INSERT INTO #tbl_transection VALUES(1,1231,1222,55000,'2021-01-18',2)
INSERT INTO #tbl_transection VALUES(2,1231,1222,55000,'2021-01-18',2)
INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3)
INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5)
;WITH cte
as(
SELECT NULL Trans_ID,NULL Trans_Type,NULL TransDate,'Opening' Trans_Remarks,NULL Code,NULL Head,null Debit,null Credit,isnull(opening,0) Balance
from #tbl_COA
where Level_Four_ID=1222
UNION ALL
SELECT T.Trans_ID,ty.trans_type_name as Trans_Type,Convert(varchar, T.Trans_Date ,101)as TransDate,
(CONCAT(COA.Level_Four_Name ,' ','Online Receipt C/O',' ',COAc.Level_Four_Name,' ', 'Rs. ',T.Trans_Amount)) as Trans_Remarks,
T.Level_Four_ID_C as Code ,COAc.Level_Four_Name as Head, T.Trans_Amount as Debit, CAST(0 AS decimal(18,2)) as Credit,T.Trans_Amount- CAST(0 AS decimal(18,2)) Balance
FROM #tbl_transection T
inner join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D
inner join #tbl_COA COAc on COAc.Level_Four_ID=T.Level_Four_ID_C
inner join #tbl_trans_type ty on ty.trans_type_ID=T.Trans_Type_ID
where COA.Level_Four_ID=1222
UNION ALL
SELECT T.Trans_ID,ty.trans_type_name as Trans_Type, Convert(varchar, T.Trans_Date ,101)as TransDate,
(CONCAT(COA.Level_Four_Name ,'Online Receipt C/O',' ',COA.Level_Four_Name, '',T.Trans_Amount)) as Trans_Remarks,
T.Level_Four_ID_D as Code,COA.Level_Four_Name as Head, CAST(0 AS decimal(18,2)) as Debit, Trans_Amount as Credit,CAST(0 AS decimal(18,2))-Trans_Amount Balance
FROM #tbl_transection T
inner join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D
inner join #tbl_trans_type ty on ty.trans_type_ID=T.Trans_Type_ID
where T.Level_Four_ID_c=1222
),cte1 as (
SELECT Trans_ID,Trans_Type,TransDate,Trans_Remarks, Code,Head,Debit,Credit,sum(Balance) over (order by (select null) ROWS UNBOUNDED PRECEDING) Balance
FROM cte)
select * from cte1
UNION ALL
SELECT NULL ,NULL ,NULL , null,null, 'Total',sum(Debit),sum(Credit),null
from cte1 order by Trans_ID
Ouput