SQL Server: select newest rows who's sum matches a value

Question

Here is a table...

ID    QTY    DATE    CURRENT_STOCK
----------------------------------
1     1      Jan     30
2     1      Feb     30
3     2      Mar     30
4     6      Apr     30
5     8      May     30
6     21     Jun     30

I need to return the newest rows whose summed qty equal or exceed the current stock level, excluding any additional rows once this total has been reached, so I am expecting to see just these rows...

ID    QTY    DATE    CURRENT_STOCK
----------------------------------
4     6      Apr     30
5     8      May     30
6     21     Jun     30

I am assuming I need a CTE (Common Table Expression) and have looked at this question but cannot see how to translate that to my requirement.

Help!?

Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Mar 22 '17 at 14:50
You start summing QTY from desc order of a month and when it reach >= 30 you stop — Veljko89, Mar 22 '17 at 14:52

John Cappelletti · Accepted Answer · 2017-03-22T15:06:28.050

Declare @YourTable table (ID int,QTY int,DATE varchar(25), CURRENT_STOCK int)
Insert Into @YourTable values
(1     ,1      ,'Jan'     ,30),
(2     ,1      ,'Feb'     ,30),
(3     ,2      ,'Mar'     ,30),
(4     ,6      ,'Apr'     ,30),
(5     ,8      ,'May'     ,30),
(6     ,21     ,'Jun'     ,30)


Select A.*
 From  @YourTable A
 Where ID>= (
                Select LastID=max(ID)
                 From  @YourTable A
                 Cross Apply (Select RT = sum(Qty) from @YourTable where ID>=A.ID) B
                 Where B.RT>=CURRENT_STOCK
             )

Returns

ID  QTY DATE    CURRENT_STOCK
4   6   Apr     30
5   8   May     30
6   21  Jun     30

Veljko89 · Answer 2 · 2017-03-23T06:52:12.093

One way to do it with your provided data set

if object_id('tempdb..#Test') is not null drop table #Test
create table #Test (ID int, QTY int, Date_Month nvarchar(5), CURRENT_STOCK int)

insert into #Test (ID, QTY, Date_Month, CURRENT_STOCK)
values
(1, 1, 'Jan', 30),
(2, 1, 'Feb', 30),
(3, 2, 'Mar', 30),
(4, 6, 'Apr', 30),
(5, 8, 'May', 30),
(6, 21, 'Jun', 30)

if object_id('tempdb..#Finish') is not null drop table #Finish
create table #Finish (ID int, QTY int, Date_Month nvarchar(5), CURRENT_STOCK int)

declare @rows int = (select MAX(ID) from #Test)
declare @stock int = (select MAX(CURRENT_STOCK) from #Test)
declare @i int = 1
declare @Sum int = 0
while @rows > @i
BEGIN
    select @Sum = @Sum + QTY from #Test where ID = @rows 

    IF (@SUM >= @stock)
    BEGIN
        set @i = @rows + 1 -- to exit loop
    END

    insert into #Finish (ID, QTY, Date_Month, CURRENT_STOCK)
    select ID, QTY, Date_Month, CURRENT_STOCK from #Test where ID = @rows 

    set @rows = @rows - 1
END

select * from #Finish

Michael L. · Answer 3 · 2017-03-22T18:00:38.283

Setup Test Data

-- Setup test data
CREATE TABLE #Stock
    ([ID] int, [QTY] int, [DATE] varchar(3), [CURRENT_STOCK] int)
;

INSERT INTO #Stock
    ([ID], [QTY], [DATE], [CURRENT_STOCK])
VALUES
    (1, 1, 'Jan', 30),
    (2, 1, 'Feb', 30),
    (3, 2, 'Mar', 30),
    (4, 6, 'Apr', 30),
    (5, 8, 'May', 30),
    (6, 21, 'Jun', 30)
;

Solution for SQL Server 2012+

If you have a more recent version of SQL server which supports full window function syntax, you can do it look this:

-- Calculate a running total of qty by Id descending
;WITH stock AS (
    SELECT *
          -- This calculates the SUM over a 'window' of rows based on the first
          -- row in the result set through the current row, as specified by the
          -- ORDER BY clause
          ,SUM(qty) OVER(ORDER BY Id DESC 
                         ROWS BETWEEN UNBOUNDED PRECEDING 
                            AND CURRENT ROW) AS TotalQty
    FROM #Stock
),
-- Identify first row in mininum set that matches or exceeds CURRENT_STOCK
first_in_set AS (
    SELECT TOP 1 *    
    FROM stock
    WHERE TotalQty >= CURRENT_STOCK
)
-- Fetch matching set
SELECT *
FROM #stock
WHERE Id >= (SELECT Id FROM first_in_set)

Solution for SQL Server 2008

For SQL Server 2008, which only has basic support for window functions, you can calculate the running total using CROSS APPLY:

-- Calculate a running total of qty by Id descending
;WITH stock AS (
    SELECT *
          -- This window function causes the results of this query
          -- to be sorted in descending order by Id
          ,ROW_NUMBER() OVER(ORDER BY Id DESC) AS sort_order   
    FROM #Stock s1
    -- CROSS APPLY 'applies' the query (or UDF) to every row in a result set
    -- This CROSS APPLY query produces a 'running total'
    CROSS APPLY (
        SELECT SUM(Qty) AS TotalQty
        FROM #Stock s2
        WHERE s2.Id >= s1.id
    ) total_calc
    WHERE TotalQty >= s1.CURRENT_STOCK
),
-- Identify first row in mininum set that matches or exceeds CURRENT_STOCK
first_in_set AS (
    SELECT TOP 1 Id
    FROM stock
    WHERE sort_order = 1
)
-- Fetch matching set
SELECT *
FROM #stock
WHERE Id >= (SELECT Id 
             FROM first_in_set)

sql-server-2008 tag indicates it does not support `sum() over()` with an `order by` clause. — SqlZim, Mar 22 '17 at 15:31
Good catch @SqlZim I didn't notice the version tag. Unfortunately, this solution isn't supported in SQL Server 2008. I'll leave this answer in case it helps someone with a newer version. — Michael L., Mar 22 '17 at 15:35
Ah, I hadn't seen the answer by @JohnCapelletti before I posted my update. It is virtually identical to my SQL Server 2008 answer, but briefer. You should accept his answer. — Michael L., Mar 22 '17 at 18:04

SQL Server: select newest rows who's sum matches a value

3 Answers3

Setup Test Data

Solution for SQL Server 2012+

Solution for SQL Server 2008

Linked