基于PERSON_ID与SERVICE_RANK分组合并连续日期区间的SQL需求

Got it, this is a classic gaps and islands scenario in SQL—exactly what you need to group consecutive rows with the same SERVICE_RANK while keeping non-consecutive same-rank segments separate. Let's break down how to solve this with window functions (no cursors required!):

Step-by-Step Explanation #

The core idea is to use two ROW_NUMBER() calculations to create a grouping key that stays consistent for consecutive rows of the same SERVICE_RANK, and changes when the rank switches. Here's how it works:

• First, assign a row number partitioned by PERSON_ID and SERVICE_RANK, ordered by START_DATE
• Second, assign a row number partitioned only by PERSON_ID, ordered by START_DATE
• The difference between these two row numbers will be identical for all consecutive rows in the same "island" of SERVICE_RANK

Full SQL Code (Tested with Your Sample Data) #

DECLARE @TestDATA TABLE (PERSON_ID int, START_DATE date, END_DATE date,SERVICE_RANK int)
INSERT INTO @TestDATA VALUES
(123, '2018-01-31', '2018-02-14', 7),
(123, '2018-03-28', '2018-04-11', 4),
(123, '2018-04-12', '2018-04-30', 4),
(123, '2018-05-25', '2018-06-08', 7),
(123, '2018-06-08', '2018-06-15', 7),
(123, '2018-06-19', '2018-06-26', 7),
(123, '2018-06-26', '2018-09-28', 4),
(123, '2018-10-10', '2018-11-07', 7),
(123, '2018-11-27', '2018-12-11', 7),
(123, '2018-12-11', '2018-12-24', 7);

WITH RankedData AS (
    SELECT 
        PERSON_ID,
        START_DATE,
        END_DATE,
        SERVICE_RANK,
        -- Row number within same person + rank
        ROW_NUMBER() OVER (PARTITION BY PERSON_ID, SERVICE_RANK ORDER BY START_DATE) AS RankWithinGroup,
        -- Row number within just the person
        ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY START_DATE) AS OverallRowNum
    FROM @TestDATA
),
GroupedData AS (
    SELECT 
        PERSON_ID,
        START_DATE,
        END_DATE,
        SERVICE_RANK,
        -- The difference creates a unique key for each consecutive rank group
        OverallRowNum - RankWithinGroup AS GroupKey
    FROM RankedData
)
SELECT 
    PERSON_ID,
    START_DATE,
    END_DATE,
    SERVICE_RANK,
    -- Assign a dense rank to each group key per person to get SERVICE_PERIOD
    DENSE_RANK() OVER (PARTITION BY PERSON_ID ORDER BY GroupKey) AS SERVICE_PERIOD
FROM GroupedData
ORDER BY PERSON_ID, START_DATE;

Expected Output #

This query will produce exactly the result you're looking for:

Why This Works #

• The GroupKey value stays the same as long as the SERVICE_RANK is consecutive for a person. When the rank changes, the RankWithinGroup resets, making the difference (OverallRowNum - RankWithinGroup) jump to a new value.
• DENSE_RANK() then converts these unique GroupKey values into sequential SERVICE_PERIOD numbers per person, which handles all edge cases (like repeated ranks with gaps, single-rank rows, or contiguous date overlaps).

This approach is efficient even for large datasets (tens of thousands of rows) since it uses set-based operations instead of cursors, which are much slower for bulk data.

内容的提问来源于stack exchange，提问作者MattLincoln