多日期范围求和问题：6个月内分月统计厂商产品需求

原代码核心缺陷 #

1. 分组逻辑错误：GROUP BY 包含 o.CLOSEDATE，导致数据按「产品+具体关闭日期」拆分，无法实现按产品维度汇总各月份需求总量。
2. 聚合函数用法错误：CASE 语句放在 SUM 外部，仅对符合条件的行返回求和值，不符合的返回 NULL，而非仅统计对应月份的有效数量。

修正后的SQL代码 #

@Manufacturer VARCHAR(10)

AS
BEGIN
    SET NOCOUNT ON

    SELECT 
        p.PRODUCTCODE AS '产品编码',
        SUM(CASE WHEN o.CLOSEDATE BETWEEN GETDATE() AND DATEADD(MONTH, +1, GETDATE()) THEN ql.QUANTITY ELSE 0 END) AS '第1月需求量',
        SUM(CASE WHEN o.CLOSEDATE BETWEEN DATEADD(MONTH, +1, GETDATE()) AND DATEADD(MONTH, +2, GETDATE()) THEN ql.QUANTITY ELSE 0 END) AS '第2月需求量',
        SUM(CASE WHEN o.CLOSEDATE BETWEEN DATEADD(MONTH, +2, GETDATE()) AND DATEADD(MONTH, +3, GETDATE()) THEN ql.QUANTITY ELSE 0 END) AS '第3月需求量',
        SUM(CASE WHEN o.CLOSEDATE BETWEEN DATEADD(MONTH, +3, GETDATE()) AND DATEADD(MONTH, +4, GETDATE()) THEN ql.QUANTITY ELSE 0 END) AS '第4月需求量',
        SUM(CASE WHEN o.CLOSEDATE BETWEEN DATEADD(MONTH, +4, GETDATE()) AND DATEADD(MONTH, +5, GETDATE()) THEN ql.QUANTITY ELSE 0 END) AS '第5月需求量',
        SUM(CASE WHEN o.CLOSEDATE BETWEEN DATEADD(MONTH, +5, GETDATE()) AND DATEADD(MONTH, +6, GETDATE()) THEN ql.QUANTITY ELSE 0 END) AS '第6月需求量'
    FROM OPPORTUNITY AS o
    JOIN CUSTOMEROPPORTUNITY AS co (NOLOCK) ON co.ID = o.ID
    JOIN CUSTOMER AS c (NOLOCK) ON co.CUSTOMER = c.ID
    JOIN ENTITYRELATIONSHIP AS er (NOLOCK) ON er.C_ENTITY1 = o.ID
    JOIN QUOTATION AS sq (NOLOCK) ON er.ENTITY2 = sq.ID
    JOIN SALESQUOTATIONLINE AS ql (NOLOCK) ON ql.OWNER = sq.ID
    JOIN PRODUCT AS p (NOLOCK) ON p.ID = ql.PRODUCT
    JOIN MANUFACTURER AS m (NOLOCK) ON m.ID = p.MANUFACTURER
    JOIN OPPORTUNITYSOURCE AS os (NOLOCK) ON o.OPPORTUNITYSOURCE = os.ID
    JOIN OPPORTUNITYSTATUS AS wf (NOLOCK) ON o.WORKFLOWSTATUS = wf.ID
    WHERE m.CODE = @Manufacturer 
      AND wf.CODE != 'LOST' 
      AND o.CLOSEDATE BETWEEN GETDATE() AND DATEADD(MONTH, +6, GETDATE())
    GROUP BY p.PRODUCTCODE
END

关键修正说明 #

• 将 CASE 嵌套进 SUM 函数内部，仅对对应月份的数量求和，不符合条件的记为0，确保各月份数据独立统计。
• 移除 GROUP BY 中的 o.CLOSEDATE 和 m.CODE，仅按产品编码分组，保证每个产品一行，汇总全6个月的需求数据。
• 缩小 WHERE 子句的日期范围至未来6个月，减少不必要的数据查询开销。

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