SQL Server 2008 query to sum over dates and star at 0 if threshold is reached

Question

I have a table in SQL Server 2008;

DateStamp       QTY
1/1/2014        1
1/5/2014        2
1/6/2014        3
1/7/2014        2
1/8/2014        3
1/11/2014       1
1/20/2014       6
1/21/2014       4
1/25/2014       1
1/26/2014       2
1/28/2014       3
1/29/2014       2
2/2/2014        3
2/3/2014        1
2/7/2014        6
2/8/2014        4
2/15/2014       1
2/19/2014       2
2/20/2014       3
2/23/2014       2

I would like to have a result like:

sum till minimum threshold is reached lets say 10 for the example (

then show and start over (start summing from zero threafter)

In between result result should be something like;

DateStamp       QTY     SUBtotal
1/1/2014        1       
1/5/2014        2       
1/6/2014        3       
1/7/2014        2       
1/8/2014        3       11
1/11/2014       1       
1/20/2014       6       
1/21/2014       4       11
1/25/2014       1       
1/26/2014       2       
1/28/2014       3       
1/29/2014       2       
2/2/2014        3       11
2/3/2014        1       
2/7/2014        6       
2/8/2014        4       11
2/15/2014       1       
2/19/2014       2       
2/20/2014       3       
2/23/2014       2       
2/26/2014       1       
3/1/2014        1       10

Final result

DateStamp       total
1/8/2014        11
1/21/2014       11
2/2/2014        11
2/8/2014        11
3/1/2014        10

Thank you

Richard

EDIT: (from comment)

SELECT 
    t0.*, 
    SUM(t1.qty), 
    CASE 
        WHEN SUM(t1.qty) > 5 THEN SUM(t0.qty) ELSE 0 
    END AS subtotal
FROM 
    (SELECT 
        *, 
        ROW_NUMBER() OVER(ORDER BY DATESTAMP asc) AS RowNumber
     FROM dbo.DATESEARCH) AS t0 
INNER JOIN
     (SELECT 
         *, 
         ROW_NUMBER() OVER(ORDER BY DATESTAMP asc) AS RowNumber
      FROM dbo.DATESEARCH) AS t1 ON t1.RowNumber <= t0.RowNumber
GROUP BY 
    t0.RowNumber, t0.DATESTaMP, t0.qty
HAVING 
    SUM(t1.qty) > 10

yes I found following code, but this does not start at 0 when the treshold has been reached: SELECT t0.* , SUM(t1.cbm) , case when SUM(t1.CBM)> 5 then SUM(t0.CBM)else 0 end AS AccumulatedMoney FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY DATESTAMP asc) AS RowNumber FROM dbo.DATESEARCH ) AS t0 inner JOIN ( SELECT *, ROW_NUMBER() OVER(ORDER BY DATESTAMP asc) AS RowNumber FROM dbo.DATESEARCH ) AS t1 ON t1.RowNumber <= t0.RowNumber GROUP BY t0.RowNumber, t0.DATESTaMP, t0.cbm HAVING SUM(t1.cbm) > 10 — user3332662, Feb 20 '14 at 13:29
@user3332662 . . . Getting the cumulative sum is relatively easy. Doing what you want is quite hard in SQL (if possible, it requires either massive joins or complex recursive CTEs). The problem is the "remainder" -- if the extra "1" were applied to the sum, then the problem would be much easier to solve. — Gordon Linoff, Feb 20 '14 at 13:54
i don not get what you mean with "if the extra "1" were applied to the sum, " maybe my language is wrong, but my treshold (10) is the minimum it needs to reach — user3332662, Feb 20 '14 at 13:56
Solution here might help you:http://stackoverflow.com/questions/14508629/select-running-total-until-specific-sum-is-reached — Deepshikha, Feb 20 '14 at 14:03
Actually, this sounds like it should be a variation of a gaps-and-islands problem, just using `SUM()` instead of/along with `ROW_NUMBER()`.... still got nothing at the moment, though. Would at minimum require joining a running sum to itself. — Clockwork-Muse, Feb 20 '14 at 14:11

score 0 · Accepted Answer · answered Feb 20 '14 at 14:08

I agree with Gordon, cumulative sum is easy in a single SQL-statement. What you need is probably some more advanced TSQL. Not optimal, but at least, saves you a few roundtrips to your server.

DECLARE @result TABLE (datestamp DATETIME, qty INT)
DECLARE @datestamp DATETIME, @qty INT, @qty_sum INT = 0 
DECLARE iterator CURSOR LOCAL FAST_FORWARD FOR 
    SELECT datestamp,qty FROM mytable ORDER BY datestamp

OPEN iterator
FETCH NEXT FROM iterator INTO @datestamp,@qty
WHILE @@FETCH_STATUS = 0 BEGIN
    IF (@qty + @qty_sum > 10) BEGIN
        INSERT @result VALUES (@datestamp, @qty + @qty_sum)
        SET @qty_sum = 0
    END ELSE BEGIN
        SET @qty_sum += @qty
    END

    FETCH NEXT FROM iterator INTO @datestamp,@qty
END
IF (@qty_sum > 0) BEGIN
    INSERT @result VALUES (@datestamp, @qty_sum)
END 
CLOSE iterator
DEALLOCATE iterator

SELECT * FROM @result ORDER BY datestamp

Hi Fredrik, it works.. do not really understand the code, but thanks helps a lot!! — user3332662, Feb 20 '14 at 14:29

SQL Server 2008 query to sum over dates and star at 0 if threshold is reached

1 Answers1