计算多次任职员工总任职时长：SQL性能优化需求

数据集背景 #

我有一份从2018年1月1日开始的员工雇佣与离职历史数据集，2018年之前无完整历史记录，但保留了初始雇佣日期（Employee Original Hire Date）和连续服务日期（Service Date）。例如员工123456于2018年2月15日离职，初始雇佣日期为2014年1月9日；但2018年前离职且未重新雇佣的员工无相关数据。

计算逻辑 #

• 始终以初始雇佣日期和连续服务日期中的较早者作为首次雇佣日期。
• 若首次雇佣日期早于2018年，且员工在数据库中的首个操作是重新雇佣（Rehire Employee），则假设2018年1月1日为首次离职日期。
• 统计2018年1月1日之后的雇佣与离职记录。
• 累加员工所有任职阶段（stint）的工作天数。

示例员工数据 #

注：Voluntary和Involuntary均指离职（Termed）

中间计算步骤 #

最终输出为所有stintlength的总和，当前逻辑可行，但运行速度极慢，仅能达到每分钟处理3500行，需要将性能提升10倍。

现有代码 #

DECLARE @eeid int
DECLARE @lastupdated date
DECLARE @firsttrend date
DECLARE @firststatus nvarchar(50)
DECLARE @firstservicedate date
DECLARE @activejan2018 bit
DECLARE @mintermdate date
DECLARE @firsttermdate date
DECLARE @return int

SET @eeid = 143914
SET @lastupdated = (SELECT MAX(lastupdated) FROM cleandata.employeefullhistory2)
SET @firsttrend = (SELECT MIN(Employee_Trend_Date) FROM cleandata.employeefullhistory2 WHERE Employee_ID = @eeid)
SET @mintermdate = (SELECT MIN(Employee_Trend_Date) FROM cleandata.employeefullhistory2 WHERE Employee_ID = @eeid AND (Employee_Action = 'Voluntary' OR Employee_Action = 'Involuntary'))
SET @firstservicedate = (SELECT IIF(MIN(Employee_Original_Hire_Date)>MIN(Service_Date),MIN(Service_Date),MIN(Employee_Original_Hire_Date)) FROM cleandata.employeefullhistory2 WHERE Employee_ID = @eeid)
SET @firststatus =(SELECT TOP 1 Employee_Action FROM cleandata.employeefullhistory2 WHERE Employee_ID = @eeid AND Employee_Trend_Date = @firsttrend)
SET @activejan2018 = CASE WHEN @firstservicedate <= '2018-01-01' AND NOT (@firststatus = 'Rehire Employee' OR @firststatus = 'Hire Employee') THEN 1 ELSE 0 END
SET @firsttermdate = CASE WHEN @firstservicedate <= '2018-01-01' AND @activejan2018 = 0 THEN '2018-01-01' ELSE @mintermdate END



SET @return = (SELECT SUM(stintlength) FROM (

    SELECT
        stint
        ,[Hired]
        ,CASE WHEN [Termed] IS NULL THEN @lastupdated ELSE [Termed] END Termed
        ,DATEDIFF(DAY,[Hired],CASE WHEN [Termed] IS NULL THEN @lastupdated ELSE [Termed] END) stintlength
    FROM(
        SELECT 
            t.trenddate
            ,t.status
            ,ROW_NUMBER() OVER (PARTITION BY t.status ORDER BY t.trenddate asc) stint
        FROM(
            SELECT @firstservicedate trenddate, 'Hired' status
            UNION
            SELECT @firsttermdate trenddate, 'Termed'
            UNION
            SELECT 
                Employee_Trend_Date
                ,CASE WHEN Employee_Action = 'Voluntary' OR Employee_Action = 'Involuntary' THEN 'Termed' ELSE 'Hired' END
            FROM cleandata.employeefullhistory2
            WHERE Employee_ID = @eeid AND (Employee_Action = 'Voluntary' OR Employee_Action = 'Involuntary' OR Employee_Action = 'Hire Employee' OR Employee_Action = 'Rehire Employee')
        )t 
    )t
    PIVOT (
        MIN(trenddate)
        FOR status IN ([Hired],[Termed])
    ) pt
)t)

SELECT @return

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