TSQL - Calculate difference in SQL Server per Grouping

Question

Hello,

I have a table with the following data, I need to calculate a new column(Cum Amount) with the difference of (DRAW- DIST) per each date.Need code for the same.

Answer 1

Hi @Rahul Polaboina
There are some dates in the table you gave that only have the amount of DRAW, no DIST amount. For these dates, is it calculated by defaulting DIST to 0?
If i guess right，please check this query:

;WITH CTE AS  
(  
 SELECT *,LEAD(Amount,1,0)OVER(PARTITION BY cfdate ORDER BY transcode DESC)AS DIST_Amount,  
          ROW_NUMBER()OVER(PARTITION BY cfdate ORDER BY transcode DESC) AS Row_Num  
 FROM Your_Table  
)  
SELECT cfdate,Amount AS DRAW_Amount,DIST_Amount,Amount-DIST_Amount AS Cum_Amount  
FROM CTE  
WHERE Row_Num=1  
ORDER BY cfdate  

If the answer is not working or helpful, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) along with your expected result. So that we’ll get a right direction and make some test.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Screenshots are more useless and that's to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.