[SOLVED] SQL query to calculate remaining total using previous row

SQL query to calculate remaining total using previous row

I'm trying to write a query that will calculate remaining total points for each day when some points have been completed.

Story points completed in a day

So if my total is 100 points, it should show 99, 97.5, 94.5 as I want to use it in a burndown graph in Power BI.

If I use LAG, it will calculate the remaining for each row separately, instead of using 'updated' total from previous row.

Also - for rest of the days with no change in points it should show most recent remaining total.

Any ideas?

Solution

You can use SUM() OVER window function:

With MyTbl as (
select *
from (values
   ('2022-06-08', 1.0)
  ,('2022-06-13', 1.5)
  ,('2022-06-14', 3.0)
     ) T(Created, StoryPoints)
 ) 
select 
   M.*, 
   RemainingPoints=100-SUM(StoryPoints) over (order by Created rows between unbounded preceding and current row)
from MyTbl M