SQL Server项目NetValue按年度区间汇总逻辑实现咨询

现有2019-2023的年份维度，需按年度区间将项目的NetValue累入对应年度，规则如下：

• 项目当年启动且当年结束：NetValue仅计入该年度
• 项目跨年度启动结束：NetValue计入启动年度到结束年度的所有年份
• 项目启动后enddate为空（表示持续到最大年度2023）：NetValue计入启动年度到2023的所有年份

用户通过递归CTE生成年份区间并关联表查询，但结果错误：

;WITH YearRanges AS 
(
    SELECT 2019 AS StartYear, 2019 AS EndYear
    UNION ALL
    SELECT StartYear, EndYear + 1
    FROM YearRanges
    WHERE EndYear + 1 <= 2023
)
SELECT
    YR.EndYear AS Year,
    SUM(CASE
        WHEN YEAR(T.projectstartdate) = YR.EndYear THEN cast(T.NetValue as decimal(18,2))
        WHEN YEAR(T.enddate) >= YR.EndYear THEN cast(T.NetValue as decimal(18,2))
        ELSE 0
    END) AS TotalAmount
FROM 
    YearRanges YR
JOIN 
    TrackerMainEntry T ON YEAR(T.projectstartdate) >= YR.StartYear
GROUP BY 
    YR.EndYear
OPTION (MAXRECURSION 0);

用户期望的核心匹配逻辑（示例）：

SELECT
    NetValue, projectstartdate, enddate 
FROM  
    [TrackerMainEntry] 
WHERE
    YEAR(projectstartdate) IN (2019) 

UNION

SELECT
    NetValue, projectstartdate, enddate 
FROM
    [TrackerMainEntry] 
WHERE
    YEAR(projectstartdate) IN (2019) 
    AND YEAR(enddate) IS NULL

UNION

SELECT
    NetValue, projectstartdate, enddate 
FROM
    [TrackerMainEntry] 
WHERE
    YEAR(projectstartdate) IN (2019) 
    AND YEAR(enddate) = 2022

UNION

SELECT
    NetValue, projectstartdate, enddate 
FROM
    [TrackerMainEntry] 
WHERE
    YEAR(projectstartdate) IN (2020)

提供的SampleData表结构及测试数据：

CREATE TABLE [dbo].[SampleData]
(
    [productname] [varchar](100) NULL,
    [NetValue] [varchar](100) NULL,
    [ProjectStartDate] [date] NULL,
    [EndDate] [date] NULL);
GO

INSERT [dbo].[SampleData] ([productname], [NetValue], [ProjectStartDate], [EndDate])
VALUES ('Project_22', '-11224.68', CAST('2020-03-01' AS Date), CAST('2021-12-15' AS Date)),
       ('Project_64', '261706.4', CAST('2019-11-01' AS Date), CAST('2022-08-18' AS Date)),
       ('Project_64', '21309.44', CAST('2021-01-01' AS Date), CAST('2022-08-18' AS Date)),
       ('Project_4', '3057.2', CAST('2020-03-01' AS Date), NULL),
       ('Project_39', '88298.272', CAST('2020-07-01' AS Date), CAST('2022-08-08' AS Date)),
       ('Project_33', '256230.16', CAST('2019-12-01' AS Date), CAST('2022-08-30' AS Date)),
       ('Project_10', '219442.44', CAST('2021-10-01' AS Date), CAST('2021-11-26' AS Date)),
       ('Project_61', '-18707.8', CAST('2021-06-01' AS Date), NULL),
       ('Project_44', '40444.52', CAST('2021-10-01' AS Date), CAST('2022-09-01' AS Date)),
       ('Project_37', '989082', CAST('2021-11-01' AS Date), CAST('2021-12-15' AS Date)),
       ('Project_62', '113845.344', CAST('2019-01-01' AS Date), NULL),
       ('Project_63', '143278.56', CAST('2021-05-01' AS Date), CAST('2022-09-15' AS Date)),
       ('Project_68', '33998.896', CAST('2021-05-01' AS Date), CAST('2022-08-01' AS Date)),
       ('Project_65', '56889.04', CAST('2020-04-01' AS Date), NULL),
       ('Project_56', '279507.92', CAST('2020-10-01' AS Date), NULL),
       ('Project_20', '145405.92', CAST('2022-05-01' AS Date), CAST('2022-05-27' AS Date)),
       ('Project_60', '365556.16', CAST('2022-08-22' AS Date), CAST('2022-05-27' AS Date)),
       ('Project_5', '5322.264', CAST('2020-08-01' AS Date), CAST('2022-09-01' AS Date)),
       ('Project_51', '31690.9', CAST('2020-12-01' AS Date), NULL),
       ('Project_67', '28117.984', CAST('2021-06-01' AS Date), NULL),
       ('Project_59', '10735.488', CAST('2021-03-01' AS Date), NULL),
       ('Project_12', '2974.98', CAST('2022-05-03' AS Date), CAST('2022-05-13' AS Date)),
       ('Project_29', '18307.36', CAST('2019-09-01' AS Date), NULL),
       ('Project_47', '147818.38', CAST('2020-09-01' AS Date), NULL),
       ('Project_2', '-8660.24', CAST('2021-01-01' AS Date), CAST('2021-12-15' AS Date)),
       ('Project_16', '14490.552', CAST('2020-10-01' AS Date), NULL),
       ('Project_45', '188519.088', CAST('2021-03-01' AS Date), NULL),
       ('Project_15', '161817.76', CAST('2021-02-01' AS Date), CAST('2022-09-01' AS Date)),
       ('Project_55', '39743.344', CAST('2022-01-01' AS Date), CAST('2022-05-30' AS Date)),
       ('Project_35', '139378.08', CAST('2020-12-01' AS Date), CAST('2022-08-18' AS Date)),
       ('Project_40', '12552.72', CAST('2021-01-01' AS Date), CAST('2023-10-02' AS Date)),
       ('Project_43', '9998.896', CAST('2021-07-01' AS Date), CAST('2023-10-02' AS Date)),
       ('Project_53', '94926.32', CAST('2022-08-01' AS Date), CAST('2022-01-10' AS Date)),
       ('Project_7', '35094.992', CAST('2021-03-01' AS Date), NULL),
       ('Project_50', '20534.18', CAST('2021-05-01' AS Date), CAST('2022-09-01' AS Date)),
       ('Project_8', '674.5', CAST('2020-07-01' AS Date), NULL),
       ('Project_3', '4380.568', CAST('2019-11-01' AS Date), NULL),
       ('Project_10', '42712.64', CAST('2022-09-22' AS Date), CAST('2023-10-02' AS Date)),
       ('Project_33', '129340.44', CAST('2020-10-01' AS Date), NULL),
       ('Project_33', '119190.2', CAST('2021-01-01' AS Date), NULL),
       ('Project_33', '102820.46', CAST('2021-10-01' AS Date), NULL),
       ('Project_33', '150575.48', CAST('2022-09-01' AS Date), NULL),
       ('Project_23', '55964.16', CAST('2020-06-01' AS Date), NULL),
       ('Project_21', '-16.32', CAST('2020-08-01' AS Date), NULL),
       ('Project_6', '-544.66', CAST('2021-02-01' AS Date), NULL),
       ('Project_31', '-411', CAST('2020-08-01' AS Date), NULL),
       ('Project_42', '-378.41', CAST('2021-03-01' AS Date), NULL),
       ('Project_19', '-9460.23', CAST('2020-12-01' AS Date), NULL),
       ('Project_1', '71573.1', CAST('2021-03-01' AS Date), NULL),
       ('Project_26', '282114.4', CAST('2020-12-01' AS Date), NULL),
       ('Project_63', '19964.16', CAST('2022-08-22' AS Date), NULL),
       ('Project_37', '986980', CAST('2022-05-22' AS Date), CAST('2022-09-15' AS Date)),
       ('Project_57', '1349', CAST('2021-07-01' AS Date), NULL),
       ('Project_41', '998.896', CAST('2021-06-01' AS Date), NULL),
       ('Project_17', '12489.04', CAST('2022-08-22' AS Date), NULL),
       ('Project_53', '16853.2', CAST('2022-08-01' AS Date), CAST('2022-10-07' AS Date)),
       ('Project_20', '21003.184', CAST('2022-11-01' AS Date), CAST('2023-02-03' AS Date)),
       ('Project_37', '15302.56', CAST('2022-10-01' AS Date), NULL),
       ('Project_28', '60000', CAST('2023-01-01' AS Date), CAST('2022-10-07' AS Date)),
       ('Project_15', '108967.68', CAST('2022-12-01' AS Date), CAST('2023-02-01' AS Date));
GO

实现思路 #

1. 生成2019-2023的所有独立年份作为基础维度
2. 关联项目表时，判断当前年份是否落在项目的有效周期内：
   • 项目的有效起始年：YEAR(ProjectStartDate)
   • 项目的有效结束年：若EndDate为空则取2023，否则取YEAR(EndDate)
   • 当当前年份在[有效起始年, 有效结束年]范围内时，将该项目的NetValue计入对应年份
3. 过滤异常数据：排除EndDate早于ProjectStartDate的无效项目

正确SQL代码 #

-- 生成2019-2023的年份维度
WITH Years AS (
    SELECT 2019 AS Year
    UNION ALL
    SELECT Year + 1
    FROM Years
    WHERE Year + 1 <= 2023
)
SELECT
    Y.Year,
    -- 汇总符合条件的NetValue，转换为decimal类型确保精度
    SUM(CAST(S.NetValue AS DECIMAL(18,2))) AS TotalAmount
FROM Years Y
-- 关联项目表，筛选当前年份在项目有效周期内的记录
JOIN SampleData S 
    ON Y.Year BETWEEN YEAR(S.ProjectStartDate) 
                  AND ISNULL(YEAR(S.EndDate), 2023)
-- 过滤结束日期早于启动日期的异常项目
WHERE YEAR(S.ProjectStartDate) <= ISNULL(YEAR(S.EndDate), 2023)
GROUP BY Y.Year
ORDER BY Y.Year
OPTION (MAXRECURSION 0);

说明 #

• 使用ISNULL(YEAR(S.EndDate), 2023)统一处理未结束项目的结束年份，简化逻辑
• 通过BETWEEN直接判断年份是否在有效周期内，逻辑清晰且准确
• 增加异常数据过滤条件，避免错误计入无效项目

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