MS SQL Server中基于服务起止日期获取财年的技术问题

你的原代码通过CASE语句只能返回单个财年值，无法处理跨财年场景下生成多行记录的需求（比如6月30日到7月1日的服务需要输出两个财年）。核心问题是原逻辑没有生成财年序列并判断服务周期与财年的交集。

财年规则确认 #

根据你的示例，财年划分规则为：

• 每年7月1日至次年6月30日为一个财年，财年编号为该周期的结束年份（例如2022年6月30日属于2022财年，2022年7月1日属于2023财年）

修正后的SQL代码 #

WITH FiscalYears AS (
    -- 生成2010-2030的财年范围，可根据实际业务调整年份区间
    SELECT 
        fy.FiscalYear,
        DATEFROMPARTS(fy.FiscalYear - 1, 7, 1) AS FiscalStartDate,
        DATEFROMPARTS(fy.FiscalYear, 6, 30) AS FiscalEndDate
    FROM (
        SELECT TOP (21) 2010 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS FiscalYear
        FROM sys.all_columns
    ) fy
),
ClientServices AS (
    -- 替换为你的实际表连接逻辑，获取客户ID、服务起止日期
    SELECT 
        cp.document_id AS Client_ProfileID,
        pe.ServiceStartDate,
        -- 处理无结束日期的情况：将结束日期设为开始日期，仅匹配对应财年
        ISNULL(pe.ServiceEndDate, pe.ServiceStartDate) AS ServiceEndDate
    FROM 
        -- 此处替换为你的实际表关联
        ClientProfile cp
        JOIN ProjectEvents pe ON cp.related_id = pe.related_id
)
SELECT DISTINCT
    cs.Client_ProfileID,
    fy.FiscalYear
FROM 
    ClientServices cs
    INNER JOIN FiscalYears fy 
        ON cs.ServiceStartDate <= fy.FiscalEndDate
        AND cs.ServiceEndDate >= fy.FiscalStartDate
ORDER BY 
    cs.Client_ProfileID,
    fy.FiscalYear;

代码说明 #

1. FiscalYears CTE：生成连续的财年及其起止日期，覆盖业务中可能涉及的所有财年区间。
2. ClientServices CTE：统一处理服务结束日期为空的场景，确保仅匹配开始日期对应的财年。
3. 关联逻辑：通过判断服务周期与财年的时间交集（服务开始≤财年结束 且 服务结束≥财年开始），自动为每个重叠的财年生成一行记录。

验证示例场景 #

当服务开始日期为2022-06-30、结束日期为2022-07-01时，代码会返回：

完全符合预期需求。

内容的提问来源于stack exchange，提问作者Colin M. Lynch