Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
Are the StartSeq values guaranteed to be consecutive? –  ypercube Jan 25 '13 at 15:43
    
No. The first entry will always be 0 for StartSeq but the EndSeq could any number >=0. So the EndSeq for the first record could be 4 and the StartSeq for the next record in that GroupID would be 5. –  mheptinstall Jan 25 '13 at 15:53
    
Perhaps you can edit the sample data, to reflect this. –  ypercube Jan 25 '13 at 15:58
3  
this is a very common problem. Google up "gaps and islands" in SQL. Itzik Ben-Gan wrote a lot about such problems. –  A-K Jan 25 '13 at 16:08
    
I have updated the example and script data. Thanks for the suggestion, I will take a look at these articles. –  mheptinstall Jan 25 '13 at 19:36

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.