Thinking outside the box

Calculating Running Streak over many records

It has come to my attention that sometimes there are more than 100,000 records for which a "running streak" should be calculated on, so my previous blog post http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx will not do.
But this will work, and work fast!

DECLARE  @Sample TABLE
        (
            Col1 INT,
            Col2 INT,
            Col3 INT,
            Col4 INT,
            Col5 INT,
            Col6 INT,
            Col7 INT,
            Col8 DATETIME
        )

INSERT  @Sample
SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:00' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:05' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:10' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:15' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:20' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:25' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:30' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:35' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:40'

SELECT      w.Col7,
            MIN(w.Col8)
FROM        (
                SELECT  Col7,
                        Col8,
                        ROW_NUMBER() OVER (ORDER BY Col8) AS recID
                FROM    @Sample
            ) AS w
INNER JOIN  (
                SELECT  Col7,
                        Col8,
                        ROW_NUMBER() OVER (PARTITION BY Col7 ORDER BY Col8) AS recID
                FROM    @Sample
            ) AS x ON x.Col7 = w.Col7
                AND x.Col8 = w.Col8
GROUP BY    w.Col7,
            w.recID - x.recID
ORDER BY    MIN(w.Col8)

Print | posted on Wednesday, August 12, 2009 7:24 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]