question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked EchoLiu-msft answered

Expected OutPut require

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')



91865-image.png


sql-server-generalsql-server-transact-sql
image.png (7.9 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you tell me how to calculate the value of total?And how is the value of [Description] determined?

0 Votes 0 ·

(QTY * Rate) = Total, Sum of Total column =Net Total , Pending will be in Pending column,then Pending + NetTotal = Grand Total,
it does not need to come in above format,you may display it in row

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered EchoLiu-msft edited

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:
91818-image.png


Regards
Echo


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



image.png (6.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.