SQL Select: how to return count of consecutive values from an ordered data set

Question

The following is a subset of a table I have in a SQL Server 2008 database.

I am trying to output the Serial Number, the LID with the greatest count of consecutive LID values, and the actual count. Important to note that the table is ordered by the Last Updated Date value descending (this condition is critical). It can be grouped by Serial Number or ordered by Serial Number ascending or descending... whatever is more efficient and makes sense.

Here's what the data looks like:

[Serial Number]  [LID]   [Last Updated Date]
--------------------------------------    
123456            AAA     2012-09-24
123456            AAA     2012-09-23
123456            AAA     2012-09-22
123456            AAA     2012-09-21
123456            BBB     2012-09-20
123456            BBB     2012-09-19
123456            AAA     2012-09-18
123456            AAA     2012-09-17
123456            AAA     2012-09-16
234567            BBB     2012-09-24
234567            BBB     2012-09-23
234567            AAA     2012-09-22

The desired output to the table is:

[Serial Number]     [LID]     [LID Count]
-------------------------------------------    
123456            AAA     4
234567            BBB     2

I am at a loss. I've tried using

ROW_NUMBER() OVER(PARTITION BY [Service Tag], [LID]
                  ORDER BY [Last Updated Date] DESC) 

but all that does is break up my descending date order and I end up with the count and the LID that occurs the most during the range of dates.

Thanks in advance for any assistance you can provide!

Best Regards,

VP

Answer 1

Have a look at the example below

SQL Fiddle Demo

DECLARE @Table TABLE(
        [Serial Number] INT,
        [LID] VARCHAR(50),
        [Last Updated Date] DATETIME
)

INSERT INTO @Table SELECT 123456,'AAA','2012-09-24'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-23'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-22'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-21'
INSERT INTO @Table SELECT 123456,'BBB','2012-09-20'
INSERT INTO @Table SELECT 123456,'BBB','2012-09-19'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-18'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-17'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-16'
INSERT INTO @Table SELECT 234567,'BBB','2012-09-24'
INSERT INTO @Table SELECT 234567,'BBB','2012-09-23'
INSERT INTO @Table SELECT 234567,'AAA','2012-09-22'

;WITH Vals AS (
        SELECT  *,
                ROW_NUMBER() OVER(ORDER BY [Serial Number],[Last Updated Date] DESC) ROWID
        FROM    @Table
)
, ValsNext AS (
        SELECT  v.[Serial Number],
                v.LID,
                v.[Last Updated Date],
                v.ROWID,
                MIN(vn.ROWID) NextRowID
        FROM    Vals v LEFT JOIN
                Vals vN ON  v.[Serial Number] = vn.[Serial Number]
                        AND v.LID != vn.LID
                        AND v.ROWID < vn.ROWID
        GROUP BY    v.[Serial Number],
                    v.LID,
                    v.[Last Updated Date],
                    v.ROWID
)
, ValDiffs AS (
        SELECT  vn.[Serial Number],
                vn.LID,
                vn. NextRowID - vn.ROWID Consecutive
        FROM    ValsNext vn
)
, Serials AS (
        SELECT  [Serial Number],
                MAX(Consecutive) MaxConsecutive
        FROM    ValDiffs
        GROUP BY    [Serial Number]
)
SELECT  vd.*
FROM    Serials s INNER JOIN
        ValDiffs vd ON  s.[Serial Number] = vd.[Serial Number]
                    AND s.MaxConsecutive = vd.Consecutive