T-sql Loop Through All Rows And Sum Amount From Column Until Value Is Reached

I have a table containing the below test data: I now would like to fill a restaurant with 12 seating spaces. This should result in: Basically, I need to loop from top to bottom t

Solution 1:

Because you want to skip rows, you need a recursive CTE. But it is tricky -- because you may not have a group following your rules that adds up to exactly 12.

So:

with tn as (
         select t.*, row_number() over (orderby userid) as seqnum
         from t
        ),
       cte as (
        select userId, name, amountPersons as total, 1as is_included, seqnum
        from tn 
        where seqnum = 1
        union all
        select tn.userId, tn.name, 
               (casewhen tn.amountPersons + cte.total <= 12then tn.amountPersons + cte.total
                     else cte.total
                end),
               (casewhen tn.amountPersons + cte.total <= 12then1else0end) as is_included,
               tn.seqnum
        from cte join
             tn
             on tn.seqnum = cte.seqnum + 1where cte.total < 12
    )
select cte.*
from cte
where is_included = 1;

Here is a db<>fiddle.

Note that if you change "I" to a larger value, then it is not included and the number of occupied seats is 11, not 12.