select records upto a running total (running sum)

Question

I have a table similar to this in my Azure SQL Database

itemId	qty
1	19
2	22
3	21
4	113
5	8
6	25

I have one input value X (E.g. X = 20)

Now all I want is to write a select statement to select all the itemIds of which the total qty should be >= X

So, if X=20, Item 1 and 2 should be selected, because sum(qty) will be 41 which is > 20 similarly if X = 50, then item 1, 2 and 3 should be selected, and if we do not have that much qty (X=500) then all records should be selected,

Sequence does not matter, example ,for X=20 valid answers are

 1. item 1, item 2 (total sum = 41)
 2. item 2 only (total sum = 22)
 3. item 3 only (total sum = 21)
 4. item 4 only (total sum = 113)
 5. item 6 only (total sum = 25)

Answer 1

Seems like you could use a windowed SUM here to get the running total up to the prior row and then return rows where that total is less than your threshold:

DECLARE @Threshold int = 20;

WITH CTE AS(
    SELECT V.ItemID,
           V.Quantity,
           ISNULL(SUM(V.Quantity) OVER (ORDER BY V.ItemID
                                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS PriorTotal
    FROM (VALUES(1,19),
                (2,22),
                (3,21),
                (4,113),
                (5,8),
                (6,25)) V(ItemID,Quantity))
SELECT ItemID,
       Quantity
FROM CTE
WHERE CTE.PriorTotal < @Threshold;