如何在SQL Server中生成序列数据？员工组织任职时长统计

现有数据 #

期望结果 #

需求说明 #

核心逻辑：

• 员工调职（Transfer）时，prev_org_start_date取其首次进入原组织的日期；
• 入职（Hire）、请假（Leave）、复职（Return）记录的prev_org_start_date填'-'；
• 请假、复职不改变员工所属组织，调职时仍取首次进入该组织的起始日期。

SQL Server 解决方案 #

WITH org_groups AS (
    SELECT 
        ID,
        org,
        event_date,
        event,
        prev_org,
        -- 标记组织变更节点：prev_org不为'-'时视为进入新组织，生成分组ID
        SUM(CASE WHEN prev_org <> '-' THEN 1 ELSE 0 END) OVER (PARTITION BY ID ORDER BY event_date) AS group_id
    FROM your_table_name
),
org_start_dates AS (
    SELECT 
        ID,
        org,
        MIN(event_date) AS start_date
    FROM org_groups
    GROUP BY ID, org, group_id
)
SELECT 
    t.ID,
    t.prev_org,
    t.org,
    t.event,
    CASE 
        WHEN t.event IN ('Hire', 'Leave', 'Return') THEN '-'
        ELSE os.start_date
    END AS prev_org_start_date,
    t.event_date
FROM your_table_name t
LEFT JOIN org_start_dates os 
    ON t.ID = os.ID 
    AND t.prev_org = os.org -- 调职时关联原组织的起始日期
ORDER BY t.ID, t.event_date;

逻辑说明 #

1. org_groups CTE：按员工ID分组、事件日期排序，用窗口函数标记组织变更分组——每次prev_org不为'-'时，分组ID递增，将同一组织的连续任职记录归为一组；
2. org_start_dates CTE：按员工ID、组织、分组ID聚合，取每组最小event_date作为该组织的首次任职起始日期；
3. 最终查询：关联原表与起始日期表，根据事件类型填充prev_org_start_date，调职记录取原组织起始日期，其余事件填'-'。

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