Counting consecutive rows - SQL Server 2008

Question

I need to count consecutive rows based on value in other column.

I'm totally newbish when it comes to SQL - so ask on if something is unclear.

I have SQL Server 2008, so LEAD() and LAG() don't come into play, same with OVER() with window size base on partition.

Example data set:

SLOT                    Blocked 
-------------------------------
2015-02-09 07:00:00.000 0
2015-02-09 07:15:00.000 0
2015-02-09 07:30:00.000 0
2015-02-09 07:45:00.000 1
2015-02-09 08:00:00.000 1
2015-02-09 08:15:00.000 1
2015-02-09 08:30:00.000 0
2015-02-09 08:45:00.000 0
2015-02-09 09:00:00.000 0
2015-02-09 09:15:00.000 0
2015-02-09 09:30:00.000 1
2015-02-09 09:45:00.000 1
2015-02-09 10:00:00.000 1
2015-02-09 10:15:00.000 1
2015-02-09 10:30:00.000 0
2015-02-09 10:45:00.000 0
2015-02-09 11:00:00.000 0
2015-02-09 11:15:00.000 0
2015-02-09 11:30:00.000 0
2015-02-09 11:45:00.000 0
2015-02-09 12:00:00.000 0

The data is ordered by SLOT. I would like to count the rows over partitions where Blocked = 0.

The result set i would like to get:

SLOT                    Blocked     RowNum
-------------------------------------------
2015-02-09 07:00:00.000 0           1
2015-02-09 07:15:00.000 0           2
2015-02-09 07:30:00.000 0           3
2015-02-09 07:45:00.000 1           0
2015-02-09 08:00:00.000 1           0
2015-02-09 08:15:00.000 1           0
2015-02-09 08:30:00.000 0           1
2015-02-09 08:45:00.000 0           2
2015-02-09 09:00:00.000 0           3
2015-02-09 09:15:00.000 0           4
2015-02-09 09:30:00.000 1           0
2015-02-09 09:45:00.000 1           0
2015-02-09 10:00:00.000 1           0
2015-02-09 10:15:00.000 1           0
2015-02-09 10:30:00.000 0           1
2015-02-09 10:45:00.000 0           2
2015-02-09 11:00:00.000 0           3
2015-02-09 11:15:00.000 0           4
2015-02-09 11:30:00.000 0           5
2015-02-09 11:45:00.000 0           6
2015-02-09 12:00:00.000 0           7

Thanks in advance!

Answer 1

For SQL Server 2008 you can solve it like this

SELECT Slot
      ,Blocked
      ,CASE WHEN Blocked = 0
                 THEN ROW_NUMBER() OVER
                 (PARTITION BY rt.RunningTotal ORDER BY Slot)
            ELSE 0 END RowNum
FROM Table1 t
CROSS APPLY (SELECT ISNULL(SUM(Blocked), 0) RunningTotal
             FROM Table1
             WHERE Slot < t.Slot
            ) rt
ORDER BY Slot

(Replace Table1 with your table name)

SQL Fiddle: http://sqlfiddle.com/#!3/659a0/28

Answer 2

Interesting question..!!

I was able to achieve this using below code:

    CREATE TABLE #TestTable(SLOT DATETIME,BLOCKED INT);

    INSERT #TestTable(SLOT,BLOCKED)
    VALUES
    ('2015-02-09 07:00:00.000' ,0),
    ('2015-02-09 07:15:00.000' ,0),
    ('2015-02-09 07:30:00.000' ,0),
    ('2015-02-09 07:45:00.000' ,1),
    ('2015-02-09 08:00:00.000' ,1),
    ('2015-02-09 08:15:00.000' ,1),
    ('2015-02-09 08:30:00.000' ,0),
    ('2015-02-09 08:45:00.000' ,0),
    ('2015-02-09 09:00:00.000' ,0),
    ('2015-02-09 09:15:00.000' ,0),
    ('2015-02-09 09:30:00.000' ,1),
    ('2015-02-09 09:45:00.000' ,1),
    ('2015-02-09 10:00:00.000' ,1),
    ('2015-02-09 10:15:00.000' ,1),
    ('2015-02-09 10:30:00.000' ,0),
    ('2015-02-09 10:45:00.000' ,0),
    ('2015-02-09 11:00:00.000' ,0),
    ('2015-02-09 11:15:00.000' ,0),
    ('2015-02-09 11:30:00.000' ,0),
    ('2015-02-09 11:45:00.000' ,0),
    ('2015-02-09 12:00:00.000' ,0)


    SELECT *
    FROM #TestTable
    ORDER BY SLOT


    ;WITH TestTableVw AS
    (SELECT SLOT,BLOCKED,ROW_NUMBER() OVER(ORDER BY  SLOT) RNum
     FROM #TestTable
    )
    SELECT T.SLOT,T.BLOCKED,CASE WHEN T.BLOCKED=0 THEN V1.RNum-ISNULL((SELECT MAX(V2.RNum) FROM TestTableVw V2 WHERE V2.RNum<V1.RNum AND V2.BLOCKED=1),0) ELSE 0 END
    FROM #TestTable T
    JOIN TestTableVw V1 ON T.SLOT=V1.SLOT

    DROP TABLE #TestTable;

I hope this will help..!!

Thanks,

Swapnil

Answer 3

Since you are using sqlserver 2008, you need to use row_number() over. The calculation is a bit complex, but this script will work from sqlserver 2005.

;WITH CTE AS
(
SELECT SLOT, row_number() over (order by SLOT)-
row_number() over (partition by blocked order by SLOT) x, blocked
 FROM 
-- replace these lines with your real table
--start test tabel
(values 
(cast( '2015-02-09 07:00:00.000' as datetime), 0)
,('2015-02-09 07:15:00.000', 0),('2015-02-09 07:30:00.000', 0)
,('2015-02-09 07:45:00.000', 1),('2015-02-09 08:00:00.000', 1)
,('2015-02-09 08:15:00.000', 1),('2015-02-09 08:30:00.000', 0)
,('2015-02-09 08:45:00.000', 0),('2015-02-09 09:00:00.000', 0)
,('2015-02-09 09:15:00.000', 0),('2015-02-09 09:30:00.000', 1)
,('2015-02-09 09:45:00.000', 1),('2015-02-09 10:00:00.000', 1)
,('2015-02-09 10:15:00.000', 1),('2015-02-09 10:30:00.000', 0)
,('2015-02-09 10:45:00.000', 0),('2015-02-09 11:00:00.000', 0)
,('2015-02-09 11:15:00.000', 0),('2015-02-09 11:30:00.000', 0)
,('2015-02-09 11:45:00.000', 0),('2015-02-09 12:00:00.000', 0)) x(SLOT,Blocked )
-- end test tabel
)
SELECT 
  SLOT, 
  BLOCKED, 
  CASE WHEN blocked = 0 
    THEN
      row_number() over (partition by x, blocked order by slot) 
    ELSE 0 end ROWNUMBER
FROM cte
ORDER BY slot