Set based solution to group multiple rows within a sequence

Question

Using SQL Server 2008 and a set based solution, how could I manipulate the following data:

GroupID     Code    StartSeq    EndSeq  StartDayNo  EndDayNo
1755549     3506    0           0       59442       59444
1755549     2928    1           3       59444       59465
1755549     2928    4           5       59465       59467
1755549     2928    6           6       59467       59481
1755549     2928    7           8       59481       59482
1755549     2928    9           9       59482       59494
1755549     3429    10          10      59494       59494
1755549     2928    11          11      59494       59496
1755549     3429    12          12      59496       59496
1755549     2928    13          13      59496       59501

To get this result:

GroupID     Code    StartSeq    EndSeq  StartDayNo  EndDayNo
1755549     3506    0           0       59442       59444
1755549     2928    1           9       59444       59494
1755549     3429    10          10      59494       59494
1755549     2928    11          11      59494       59496
1755549     3429    12          12      59496       59496
1755549     2928    13          13      59496       59501

The query I originally came up with would only group together 2 rows by using a join, in the example there are 5 rows that need to be grouped to only one.

Script below with some sample data. Note there is additional data than the original example.

DECLARE @SampleData TABLE (
    [GroupID] [int] NOT NULL,
    [Code] [varchar](4) NOT NULL,
    [StartSeq] [int] NOT NULL,
    [EndSeq] [int] NOT NULL,
    [StartDayNo] [int] NOT NULL,
    [EndDayNo] [int] NOT NULL
)

INSERT INTO @SampleData
VALUES
 (1622494, N'2082', 0, 0, 59136, 59137)
, (1622494, N'2082', 1, 1, 59137, 59167)
, (1622494, N'2934', 2, 2, 59167, 59335)
, (1622494, N'3613', 3, 3, 59335, 59350)
, (1755549, N'3506', 0, 0, 59442, 59444)
, (1755549, N'2928', 1, 3, 59444, 59465)
, (1755549, N'2928', 4, 5, 59465, 59467)
, (1755549, N'2928', 6, 6, 59467, 59481)
, (1755549, N'2928', 7, 8, 59481, 59482)
, (1755549, N'2928', 9, 9, 59482, 59494)
, (1755549, N'3429', 10, 10, 59494, 59494)
, (1755549, N'2928', 11, 11, 59494, 59496)
, (1755549, N'3429', 12, 12, 59496, 59496)
, (1755549, N'2928', 13, 13, 59496, 59501)

Answer 1

Not the prettiest query I've ever written, but it seems to get the job done without any recursion. (I've made the assumption that the output needs to be grouped by GroupID and Code.)

WITH starting AS ( --Find each "starting" entry
    SELECT
        d.GroupID,
        d.Code,
        d.StartSeq,
        d.EndSeq,
        d.StartDayNo,
        d.EndDayNo
    FROM SampleData d
        LEFT OUTER JOIN SampleData prev
            ON d.Code = prev.Code
            AND d.GroupID = prev.GroupID
            AND d.StartSeq = prev.EndSeq + 1
    WHERE prev.Code IS NULL
)
SELECT
    s.GroupID,
    s.Code,
    s.StartSeq,
    ISNULL(e.EndSeq, s.EndSeq) AS EndSeq,
    s.StartDayNo,
    ISNULL(e.EndDayNo, s.EndDayNo) AS EndDayNo
FROM starting s
    OUTER APPLY ( --Find the next highest "starting" entry
        SELECT TOP 1
            s2.*
        FROM starting s2
        WHERE s2.Code = s.Code
            AND s2.GroupID = s.GroupID
            AND s2.StartSeq > s.StartSeq
        ORDER BY s2.StartSeq ASC
    ) s2
    OUTER APPLY ( --Find the highest entry less than the next highest "starting" entry
        SELECT TOP 1 *
        FROM SampleData e
        WHERE e.Code = s.Code
            AND e.GroupID = s.GroupID
            AND (e.EndSeq < s2.StartSeq OR s2.Code IS NULL)
        ORDER BY e.EndSeq DESC
    ) e
ORDER BY s.Code, s.StartSeq