Select rows until running sum reaches specific value

Question

I have the following data:

DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT);
INSERT INTO @t VALUES
('a', '2018-01-01', 100), -- 100
('a', '2018-02-01', 100), -- 200
('a', '2018-03-01', 100), -- 300
('a', '2018-04-01', 100), -- 400
('a', '2018-05-01', 100), -- 500
('b', '2018-01-01', 150), -- 150
('b', '2018-02-01', 150), -- 300
('b', '2018-03-01', 150), -- 450
('b', '2018-04-01', 150), -- 600
('b', '2018-05-01', 150); -- 750

And a value such as 300 or 301 (a user variable or column). I want to select rows until running total of amount reaches the specified value, with the following twist:

• For 300 I want to select first 3 rows for a and first 2 rows for b
• For 301 I want to select first 4 rows for a and first 3 rows for b

This is supposed to be simple but the solutions I found do not handle the second case.

Answer 1

DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT);
INSERT INTO @t VALUES
('a', '2018-01-01', 100), -- 100
('a', '2018-02-01', 100), -- 200
('a', '2018-03-01', 100), -- 300
('a', '2018-04-01', 100), -- 400
('a', '2018-05-01', 100), -- 500
('b', '2018-01-01', 150), -- 150
('b', '2018-02-01', 150), -- 300
('b', '2018-03-01', 150), -- 450
('b', '2018-04-01', 150), -- 600
('b', '2018-05-01', 150); -- 750

DECLARE @Total INT = 301;

WITH cte AS
(
    SELECT *, SUM(amount) OVER (PARTITION BY usr ORDER BY dt) AS RunTotal
    FROM @t
)
SELECT *
FROM   cte
WHERE  cte.RunTotal - cte.amount < @Total -- running total for previous row is less
                                          -- than @Total then include current row

Answer 2

DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT);
INSERT INTO @t VALUES
('a', '2018-01-01', 100), -- 100
('a', '2018-02-01', 100), -- 200
('a', '2018-03-01', 100), -- 300
('a', '2018-04-01', 100), -- 400
('a', '2018-05-01', 100), -- 500
('b', '2018-01-01', 150), -- 150
('b', '2018-02-01', 150), -- 300
('b', '2018-03-01', 150), -- 450
('b', '2018-04-01', 150), -- 600
('b', '2018-05-01', 150); -- 750

declare @target int = 300;

with cte_RunningTotal as
(
    select  
        usr,
        dt,
        amount,
        sum(amount) over (partition by usr order by dt rows unbounded preceding) as runningTotal
    from @t 
)
select *
from cte_RunningTotal 
where runningTotal < @target + amount
order by usr, dt