I have this table and I have to sort the rest after subtraction of numbers from the started numbers 350 and break if the value is equal to 0 at the end

the expected result should look

I am stuck on my code without knowing how to get the rest like in the table

with cte as (
    select id, (SELECT sum(numbers) from TABLE t2 where t2.id <= t1.id) sumT
    from TABLE T1
  )
select sumT , ( 350 - sumT) from cte where sumT <= 350

CodePudding user response：

You get the cumulative sum with SUM OVER. Then just show all rows where the rest is at least zero.

select id, numbers, 350 - so_far as rest
from
(
  select id, numbers, sum(numbers) over (order by id) as so_far
  from mytable t
) sums
where 350 - so_far >= 0
order by id;

If it can happen that you don't get down to zero exactly, but say -10 instead and you still want to show that last line, then show all rows where the row before had a rest greater than zero:

select id, numbers, 350 - so_far as rest
from
(
  select id, numbers, sum(numbers) over (order by id) as so_far
  from mytable t
) sums
where 350 - (so_far - numbers) > 0
order by id;

And if you don't want to show the -10, but a rest of 0 then, make this:

select id, numbers, case when so_far > 350 then 0 else 350 - so_far end as rest

As of SQL Server 16, you can also use GREATEST:

select id, numbers, greatest(350 - so_far, 0) as rest

CodePudding user response：

You may use a running sum function that finds the cumulative sum of numbers over the increasing value of ID as the following:

SELECT ID, Numbers, Rest
FROM 
(
  SELECT ID, Numbers,
       350 - SUM(Numbers) OVER (ORDER BY ID) AS Rest
  FROM table_name
) T
WHERE Rest>=0

See a demo.