SQL Server running total based on change of state of a column

Question

I am trying to calculate a running total of values based upon a flip/flop column with the Start and End date/time. Here is a sample set of data:

Sample Input Data:
StartDate               EndDate                 Count   FlipFlop
2016-08-28 16:34:15.380 2016-08-28 16:34:15.990 1   0
2016-08-28 16:34:15.990 2016-08-28 16:34:17.973 0   0
2016-08-28 16:34:17.973 2016-08-28 16:35:00.463 5   0
2016-08-28 16:35:00.463 2016-08-28 16:39:30.940 48  1
2016-08-28 16:39:30.940 2016-08-28 16:39:31.113 1   1
2016-08-28 16:39:31.113 2016-08-28 16:40:00.923 5   0
2016-08-28 16:40:00.923 2016-08-28 16:41:21.123 14  1
2016-08-28 16:41:21.123 2016-08-28 16:41:21.140 1   0

Output Data:
StartDate               EndDate                 Count   FlipFlop
2016-08-28 16:34:15.380 2016-08-28 16:35:00.463 6   0
2016-08-28 16:35:00.463 2016-08-28 16:39:31.113 49  1
2016-08-28 16:39:31.113 2016-08-28 16:40:00.923 5   0
2016-08-28 16:40:00.923 2016-08-28 16:41:21.123 14  1
2016-08-28 16:41:21.123 2016-08-28 16:41:21.140 1   0

Each time there is a change to the FlipFlop column, the values in the Count column are summed, whereby the First Row of that subset of data uses the StartDate column, and the Last Row of that subset will use the EndDate column. I was able to get the summing to partially work with the FlipFlop, however, the First and Last Rows DateTime is eluding me.

Thanks, HH

Answer 1

You can use the difference of ROW_NUMBERs:

WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(ORDER BY StartDate) -
                ROW_NUMBER() OVER(PARTITION BY FlipFlop ORDER BY StartDate)
    FROM tbl
)
SELECT
    StartDate   = MIN(StartDate), 
    EndDate     = MAX(EndDate), 
    [Count]     = SUM([Count]),
    FlipFlop
FROM CTE
GROUP BY
    rn, FlipFlop
ORDER BY
    MIN(StartDate);

Here is an article by Jeff Moden that explains this solution:

• Group Islands of Contiguous Dates (SQL Spackle)

ONLINE DEMO