Search code examples
sqlsql-serverpacking

Creating a running total grouped by a statement evaluation

Using this article I have implemented a query that determines packing intervals for user events:

http://www.itprotoday.com/microsoft-sql-server/new-solution-packing-intervals-problem

I've ended up with following output: enter image description here However, the bit I can't fathom out is how to group the packed intervals by userId for each date. Basically, I need a group column on the end which increments for each row when isStart is 1, but is output the same as the previous row's when isStart is null.

Thus it should go:

Row 1: 1
Row 2: 2
Row 3: 2
Row 4: 3
Row 5: 4
Row 6: 5
Row 7: 6
Row 8: 7
Row 9: 8
Row 10: 9
Row 11: 9
Row 12: 9
Row 13: 10

... etc

This then allows me to retrieve the min start and max end for each group. I'm sure this is really obvious but I can't seem to spot it!

Solution

  • Just use a cumulative sum:

    select t.*,
       sum(isStart) over (order by start) as grp
from t;