能否用SQL Server生成带起止日期的员工序列历史数据？

原始数据 #

预期结果 #

实现方案 #

完全可以通过SQL Server实现该需求，核心利用窗口函数LEAD()获取后续事件日期，结合日期函数完成计算，具体代码如下：

1. 创建测试表并插入数据

CREATE TABLE EmployeeEvents (
    ID INT,
    previous_org VARCHAR(10),
    new_org VARCHAR(10),
    event NVARCHAR(20),
    event_date CHAR(6)
);

INSERT INTO EmployeeEvents VALUES
(1, '', 'A', '入职', '202201'),
(1, 'A', 'B', '调岗', '202207'),
(1, '', 'B', '退休', '202301'),
(2, '', 'A', '入职', '202201'),
(2, 'A', 'B', '调岗', '202207'),
(2, 'B', 'C', '调岗', '202301'),
(2, '', 'C', '退休', '202308');

2. 生成包含起止日期的查询语句

SELECT 
    ID,
    previous_org,
    new_org,
    event,
    event_date,
    event_date AS start_date,
    CASE 
        WHEN event = '退休' THEN NULL
        ELSE FORMAT(DATEADD(MONTH, -1, CAST(event_date + '01' AS DATE)), 'yyyyMM')
    END AS end_date
FROM (
    SELECT 
        *,
        LEAD(event_date) OVER (PARTITION BY ID ORDER BY event_date) AS next_event_date
    FROM EmployeeEvents
) t
ORDER BY ID, event_date;

逻辑说明 #

1. LEAD()窗口函数：按员工ID分组、按事件日期排序，获取当前行对应的下一个事件日期，以此为基础计算当前记录的结束日期。
2. 日期转换与计算：将event_date拼接01转换为标准日期格式，通过DATEADD减去一个月后，再用FORMAT转换回yyyyMM的字符串格式，得到end_date。
3. 退休事件处理：直接通过CASE语句将退休事件的end_date设为NULL，符合需求规则。

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