Running SUM in T-SQL

Question

Sorry for the bad topic, but I was not sure what to call it.

I have a table that looks like this:

+-----++-----+ | Id ||Count| +-----++-----+ | 1 || 1 | +-----++-----+ | 2 || 5 | +-----++-----+ | 3 || 8 | +-----++-----+ | 4 || 3 | +-----++-----+ | 5 || 6 | +-----++-----+ | 6 || 8 | +-----++-----+ | 7 || 3 | +-----++-----+ | 8 || 1 | +-----++-----+ 

I am trying to make a choice from this table, where every time the SUM of row1 + row2 + row3 (etc.) reaches 10, then it is “HIT” and the countdown starts again.

The requested output:

 +-----++-----++-----+ | Id ||Count|| HIT | +-----++-----++-----+ | 1 || 1 || N | Count = 1 +-----++-----++-----+ | 2 || 5 || N | Count = 6 +-----++-----++-----+ | 3 || 8 || Y | Count = 14 (over 10) +-----++-----++-----+ | 4 || 3 || N | Count = 3 +-----++-----++-----+ | 5 || 6 || N | Count = 9 +-----++-----++-----+ | 6 || 8 || Y | Count = 17 (over 10..) +-----++-----++-----+ | 7 || 3 || N | Count = 3 +-----++-----++-----+ | 8 || 1 || N | Count = 4 +-----++-----++-----+ 

How can I do this with better performance? I have no idea.

Answer 1

You can use Recursive Queries

Please note that the following query assuming that the id value is all in sequence; otherwise, use ROW_NUMBER() to create a new identifier

 WITH cte AS ( SELECT id, [Count], [Count] AS total_count FROM Table1 WHERE id = 1 UNION ALL SELECT t2.id,t2.[Count], CASE WHEN t1.total_count >= 10 THEN t2.[Count] ELSE t1.total_count + t2.[Count] END FROM Table1 t2 INNER JOIN cte t1 ON t2.id = t1.id + 1 ) SELECT * FROM cte ORDER BY id 

SQL Fiddle

Answer 2

This is too long for comment.

You cannot do this using window / analytic functions because breakpoints are not known in advance. Sometimes you can calculate breakpoints. However, in this case, the breakpoints depend on the non-linear function of the previous values ​​(I can’t imagine a better word than “non-linear” right now). That is, sometimes adding “1” to an earlier value has zero effect when calculating the current row. Sometimes this has a big effect. The implication is that the calculation should start from the very beginning and go through the data.

A slight modification of the problem would be solvable using such functions. If instead the problem was to transfer the excess amount for each group (instead of restarting the amount), the problem would be solvable using aggregate amounts (and some other frauds).

Recursive queries (which others have provided) or a sequential operation is the best way to approach this problem. Unfortunately, there is no set-based method to solve it.

Answer 3

I really hope someone can show us if this is possible using the straightforward window functions. This is a real problem.

In the meantime, this is how I will do this with recursion. This processes the spaces in the sequence and processes the edge edge of the first line already having >= 10 .

I also added a maxrecursion hint to remove the default recursion limit. But I honestly don’t know how well it will work with large amounts of data.

 with NumberedRows as ( select Id, Cnt, row_number() over(order by id) as rn from CountTable ), RecursiveCTE as ( select Id, Cnt, rn, case when Cnt >= 10 then 0 else Cnt end as CumulativeSum, case when Cnt >= 10 then 'Y' else 'N' end as hit from NumberedRows where rn = 1 union all select n.Id, n.Cnt, n.rn, case when (n.Cnt + r.CumulativeSum) >= 10 then 0 else n.Cnt + r.CumulativeSum end as CumulativeSum, case when (n.Cnt + r.CumulativeSum) >= 10 then 'Y' else 'N' end as hit from RecursiveCTE r join NumberedRows n on n.rn = r.rn + 1 ) select Id, Cnt, hit from RecursiveCTE order by Id option (maxrecursion 0) 

SQLFiddle Demo

Answer 4

How about this using current totals:

 DECLARE @Data TABLE( Id INT ,SubTotal INT ) INSERT INTO @Data VALUES(1, 5) INSERT INTO @Data VALUES(2, 3) INSERT INTO @Data VALUES(3, 4) INSERT INTO @Data VALUES(4, 4) INSERT INTO @Data VALUES(5, 7) DECLARE @RunningTotal INT = 0 DECLARE @HitCount INT = 0 SELECT @RunningTotal = CASE WHEN @RunningTotal < 10 THEN @RunningTotal + SubTotal ELSE SubTotal END ,@HitCount = @HitCount + CASE WHEN @RunningTotal >= 10 THEN 1 ELSE 0 END FROM @Data ORDER BY Id SELECT @HitCount -- Outputs 2 

After re-reading the question that I see, this does not correspond to the desired result - I will leave the answer, because it can be useful for someone looking for an example of a current general solution to this problem, which doesn’t need every line with a tag Y or N.