SQL Server中补全同一客户合同间缺失FYStartDate的SQL代码求助

以下是针对需求的SQL实现代码（基于SQL Server环境编写）：

WITH client_date_range AS (
    -- 获取每个客户的最早和最晚合同生效日期，确定财年起始日的生成范围
    SELECT 
        clientid,
        MIN(effectivedate) AS min_eff_date,
        MAX(effectivedate) AS max_eff_date
    FROM [client]
    GROUP BY clientid
),
recursive_fy_dates AS (
    -- 递归生成客户合同周期内所有的财年起始日（每年6月1日）
    SELECT 
        clientid,
        DATEFROMPARTS(
            CASE WHEN MONTH(min_eff_date) >= 6 THEN YEAR(min_eff_date) ELSE YEAR(min_eff_date) - 1 END,
            6,
            1
        ) AS fy_start_date
    FROM client_date_range
    UNION ALL
    SELECT 
        clientid,
        DATEADD(YEAR, 1, fy_start_date) AS fy_start_date
    FROM recursive_fy_dates
    JOIN client_date_range cdr 
        ON recursive_fy_dates.clientid = cdr.clientid
    WHERE DATEADD(YEAR, 1, fy_start_date) <= DATEFROMPARTS(
        CASE WHEN MONTH(cdr.max_eff_date) >= 6 THEN YEAR(cdr.max_eff_date) + 1 ELSE YEAR(cdr.max_eff_date) END,
        6,
        1
    )
),
client_with_fy AS (
    -- 为原表每条合同记录计算其所属的财年起始日
    SELECT 
        clientid,
        contractid,
        effectivedate,
        DATEFROMPARTS(
            CASE WHEN MONTH(effectivedate) >= 6 THEN YEAR(effectivedate) ELSE YEAR(effectivedate) - 1 END,
            6,
            1
        ) AS fy_start_date
    FROM [client]
)
-- 合并财年起始日记录与原合同记录，补全缺失的财年起始日
SELECT 
    rfd.clientid,
    cwf.contractid,
    cwf.effectivedate,
    rfd.fy_start_date AS FYStartDate
FROM recursive_fy_dates rfd
LEFT JOIN client_with_fy cwf 
    ON rfd.clientid = cwf.clientid 
    AND rfd.fy_start_date = cwf.fy_start_date
ORDER BY 
    rfd.clientid,
    rfd.fy_start_date,
    cwf.effectivedate

逻辑说明 #

1. client_date_range：按客户分组，获取每个客户最早和最晚的合同生效日期，用于确定需要生成的财年起始日的覆盖范围。
2. recursive_fy_dates：通过递归CTE生成该客户合同周期内所有的财年起始日（每年6月1日），确保覆盖从最早合同所属财年到最晚合同所属财年的所有起始日。
3. client_with_fy：为原表中的每条合同记录计算其对应的财年起始日——生效日期在6月1日及之后的，财年起始日为当年6月1日；生效日期在6月1日之前的，财年起始日为上一年6月1日。
4. 最终查询：将递归生成的所有财年起始日与原合同记录左连接，既保留原有的合同记录，又补全了缺失的财年起始日记录（无对应合同的记录中contractid和effectivedate为NULL），最后按客户ID、财年起始日、合同生效日期排序。

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