基于财月末的Teradata员工IsNewHire标识计算逻辑及SQL问询

需求说明 #

需在Teradata中实现员工新入职标识IsNewHire（取值'Y'/'N'）的判定，财月定义为每月22日至次月21日。

判定规则与示例 #

• 新入职有效期覆盖范围：员工入职后60天培训期涉及的所有财月，加上入职日期所在财月之后的完整财月
• 若60天跨多个财月，所有涉及的财月均标记为新入职
• 示例：入职日期为2022/5/9时，新入职有效期至2023/8/21。原因：60天结束于2023/7/9，处于7月财月（截止7/21），后续需包含完整的8月财月（截止8/21）

现有资源 #

ABC.DATE_DIM表结构与样例数据 #

该表存储日期与财月末的映射关系，样例数据如下：

DATE_DT       FISCAL_MONTH_END_DT   FISCAL_YEAR_NUM
7/23/2023     8/21/2023             2023
7/22/2023     8/21/2023             2023
7/21/2023     7/21/2023             2023
7/20/2023     7/21/2023             2023

原SQL问题分析 #

你提供的UPDATE SQL存在以下问题：

1. 关联条件(AD.AGENT_HIRE_DATE + INTERVAL '60' DAY) = DD.DATE_DT仅匹配60天后当天的财月，无法覆盖60天跨多个财月的场景
2. HireDateStop直接用MonthEndFiscal + INTERVAL '30' DAY计算，不符合财月定义（财月不是固定30天）
3. UPDATE逻辑仅匹配员工ID，未判断RECORD_START_TS对应的财月是否在新入职有效期内

修正后的判定逻辑 #

1. 对每个员工，通过DATE_DIM找到其入职日期AGENT_HIRE_DATE对应的财月末hire_fiscal_end
2. 计算入职后60天的日期hire_plus_60，再找到该日期对应的财月末plus60_fiscal_end
3. 新入职的截止财月末为plus60_fiscal_end的下一个财月末（即完整的后续财月）
4. 对于AGENT_DIM中的每条记录，判断其RECORD_START_TS对应的财月末是否在hire_fiscal_end（含）到new_hire_end_fiscal（含）之间，若是则标记IsNewHire='Y'，否则为'N'

修正后的SQL实现 #

-- 先通过CTE计算每个员工的新入职有效期对应的财月范围
WITH agent_new_hire_range AS (
    SELECT
        AD.NRDP_AGENT_ID,
        -- 入职日期对应的财月末
        DD1.FISCAL_MONTH_END_DT AS hire_fiscal_end,
        -- 入职后60天对应的财月末
        DD2.FISCAL_MONTH_END_DT AS plus60_fiscal_end,
        -- 找到60天财月末的下一个财月末（完整后续财月）
        (SELECT MIN(FISCAL_MONTH_END_DT) 
         FROM ABC.DATE_DIM 
         WHERE FISCAL_MONTH_END_DT > DD2.FISCAL_MONTH_END_DT) AS new_hire_end_fiscal
    FROM ABC.AGENT_DIM AD
    -- 关联DATE_DIM获取入职日期的财月末
    LEFT JOIN ABC.DATE_DIM DD1 
        ON CAST(AD.AGENT_HIRE_DATE AS DATE) = DD1.DATE_DT
    -- 关联DATE_DIM获取入职后60天的财月末
    LEFT JOIN ABC.DATE_DIM DD2 
        ON CAST(AD.AGENT_HIRE_DATE + INTERVAL '60' DAY AS DATE) = DD2.DATE_DT
),
-- 获取每条AGENT记录对应的财月末
agent_record_fiscal AS (
    SELECT
        TGT.NRDP_AGENT_ID,
        TGT.RECORD_START_TS,
        DD.FISCAL_MONTH_END_DT AS record_fiscal_end
    FROM ABC.AGENT_DIM TGT
    LEFT JOIN ABC.DATE_DIM DD
        ON CAST(TGT.RECORD_START_TS AS DATE) = DD.DATE_DT
)
-- 执行UPDATE更新IsNewHire字段
UPDATE TGT
FROM ABC.AGENT_DIM TGT
JOIN agent_record_fiscal ARF 
    ON TGT.NRDP_AGENT_ID = ARF.NRDP_AGENT_ID
JOIN agent_new_hire_range ANHR 
    ON TGT.NRDP_AGENT_ID = ANHR.NRDP_AGENT_ID
SET IsNewHire = CASE
    WHEN ARF.record_fiscal_end BETWEEN ANHR.hire_fiscal_end AND ANHR.new_hire_end_fiscal THEN 'Y'
    ELSE 'N'
END;

补充说明 #

• 若DATE_DIM包含所有日期的映射，上述关联逻辑可确保准确匹配每个日期对应的财月末
• 如果需要处理RECORD_START_TS跨财月的场景，可调整为判断RECORD_START_TS的日期是否落在入职财月开始到新入职截止财月末之间（财月开始日期可通过FISCAL_MONTH_END_DT反向推导：即前一个财月末的次日，也就是DATEADD(DAY, 1, (SELECT MAX(FISCAL_MONTH_END_DT) FROM ABC.DATE_DIM WHERE FISCAL_MONTH_END_DT < 当前财月末))）

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