SQL Server 2019按ID和连续员工类型分组日期的问题求助

需求：基于临时表#EmpType（包含EMPID、EmpType、PayPeriod字段），生成包含EmpID、EmpType、StartDate、EndDate的结果集，要求同一EMPID下连续相同的EmpType合并为一组，取该组的起止日期。此前使用MIN/MAX日期结合ROW_NUMBER(PARTITION BY)的方法，会错误合并中间存在其他EmpType的同类型记录。

源表脚本 #

IF (SELECT OBJECT_ID('tempdb..#EmpType')) IS NOT NULL
    DROP TABLE  #EmpType

CREATE TABLE #EmpType
(
    EMPID INT,
    EmpType VARCHAR(10), 
    PayPeriod DATETIME
)

INSERT INTO #EmpType (EMPID, EmpType, PayPeriod)
SELECT 1, 'Contractor', '2019-12-20'
UNION ALL
SELECT 1, 'Contractor', '2020-01-08'
UNION ALL
SELECT 1, 'Contractor', '2020-01-22'
UNION ALL
SELECT 1, 'Perm', '2020-05-01'
UNION ALL
SELECT 1, 'Perm', '2020-05-10'
UNION ALL
SELECT 1, 'Contractor', '2020-06-10'
UNION ALL
SELECT 1, 'Contractor', '2020-06-20'

INSERT INTO #EmpType (EMPID, EmpType, PayPeriod)
SELECT 55, 'Temp', '2019-12-20'
UNION ALL
SELECT 55, 'Temp', '2020-01-08'
UNION ALL
SELECT 55, 'Temp', '2020-01-22'
UNION ALL
SELECT 55, 'Perm', '2020-02-05'
UNION ALL
SELECT 55, 'Perm', '2020-05-01'
UNION ALL
SELECT 55, 'Perm', '2020-05-10'
UNION ALL
SELECT 55, 'Temp', '2020-06-10'
UNION ALL
SELECT 55, 'Temp', '2020-06-20'
UNION ALL
SELECT 55, 'Temp', '2020-06-29'

SELECT * FROM #EmpType

期望结果 #

解决方案 #

使用岛屿和间隙经典解法，通过两次生成行号计算组标识，再进行聚合：

WITH CTE_RowNumbers AS (
    SELECT 
        EMPID,
        EmpType,
        PayPeriod,
        -- 按EMPID排序的全局行号
        ROW_NUMBER() OVER (PARTITION BY EMPID ORDER BY PayPeriod) AS RowNumGlobal,
        -- 按EMPID+EmpType排序的分组行号
        ROW_NUMBER() OVER (PARTITION BY EMPID, EmpType ORDER BY PayPeriod) AS RowNumGroup
    FROM #EmpType
),
CTE_Groups AS (
    SELECT 
        EMPID,
        EmpType,
        PayPeriod,
        -- 行号差作为连续组的标识
        RowNumGlobal - RowNumGroup AS GroupID
    FROM CTE_RowNumbers
)
SELECT 
    EMPID,
    EmpType,
    MIN(PayPeriod) AS StartDate,
    MAX(PayPeriod) AS EndDate
FROM CTE_Groups
GROUP BY EMPID, EmpType, GroupID
ORDER BY EMPID, StartDate;

解法说明 #

1. 生成行号：
   • RowNumGlobal：对每个EMPID的记录按PayPeriod升序生成连续行号。
   • RowNumGroup：对每个EMPID+EmpType的组合按PayPeriod升序生成行号。
2. 计算组标识：RowNumGlobal - RowNumGroup的差值，对于连续相同EmpType的记录会保持一致，而EmpType变化时差值会改变，以此区分不同的连续组。
3. 聚合分组：按EMPID、EmpType和GroupID分组，取每组的最小PayPeriod作为StartDate，最大PayPeriod作为EndDate，即可得到连续类型的起止日期。

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