Expected OutPut require

Question

I need below output with data below.
Net Total is column total,then Pending is coming from table and Grand Total (Pending+Net Total)

Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name varchar(50))  
CREATE TABLE #tbl_Invoice(ID INT,Level_Four_ID int,qty int,Rate int,Pending INT,Inv_date date);  
  
Insert into #tbl_Account_L_Four values  
(1001,'akhter'),  
(1002,'Ali')  
  
INSERT INTO #tbl_Invoice VALUES  
  (1,1001,2,10,0,'01-04-2021')  
, (2,1001,15,2,25,'01-04-2021')  
, (3,1002,40,120,0,'01-04-2021')  

Answer 1

Please refer to:

;WITH cte  
 as(SELECT Level_Four_Name AS Customer,'Water' AS [Description]  
 ,ti.Qty,ti.Rate,ti.Pending,Total=ti.QTY * ti.Rate  
 FROM #tbl_Account_L_Four ta  
 JOIN #tbl_Invoice ti  
 ON ta.Level_Four_ID=ti.Level_Four_ID   
 WHERE ta.Level_Four_ID=1001)  
  
 SELECT Customer,[Description],CAST(Qty as varchar)Qty,CAST(Rate as varchar) Rate,Total   
 FROM cte  
 UNION ALL   
 SELECT '','','','Net Total',Sum(Total) FROM cte  
 UNION ALL   
 SELECT '','','','Pending',(SELECT Pending FROM cte WHERE Pending<>0)  
 UNION ALL   
 SELECT '','','','Grand Total', Sum(Total)+(SELECT Pending FROM cte WHERE Pending<>0) FROM cte  
  

Output:

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.