SQL Select: how to return count of consecutive values from an ordered data set
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
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
Here's a solution
;with cte as (
select sn, lid, ludate,
datediff(d,row_number() over (
partition by sn,lid
order by ludate),ludate) dd
from xtable
), cte2 as (
select sn,lid,dd,count(*)c,
rn=row_number() over (partition by lid
order by count(*) desc)
from cte
group by sn,lid,dd
)
select sn [Serial Number], lid, c [Count]
from cte2
where rn=1
The first CTE uses a complex DATEADD expression to group together consecutive days, tagged using the column dd .
The second CTE is a regular row numbering of the counts.