SQL按日期范围透视查询无法抓取未来月份消耗数据问题排查

核心问题原因 #

• 日期比较逻辑错误：你用FORMAT输出的yyyy-MMM是字符串格式，直接用>=比较会按字母序判断，比如2021-Oct的首字母O排在2021-Sep的S之前，字符串比较结果为2021-Oct < 2021-Sep，直接导致采购早于消耗的跨月数据被过滤，这是9月采购10月消耗数据无法抓取的核心原因。
• 透视维度错误：子查询cs中的Date字段取自采购表的采购月份，你PIVOT时按这个字段转列，相当于把所有消耗数据都统计到了采购月份对应的列中，自然无法正确展示后续月份的消耗数值。
• 语法错误：GROUP BY子句中出现了未定义的别名PT，整个查询不存在PT的表别名，属于笔误，会直接导致查询执行异常。
• 聚合重复问题：采购表按批次关联多月份消耗数据时，一行采购记录会匹配多行不同月份的消耗记录，直接SUM(VS.Quantity)会导致合同数量被重复计算，结果偏大。

修复后代码 #

SELECT * 
FROM 
(
-- 先查询每个批次的固定采购属性，避免关联消耗后重复计算
SELECT 
    VS.[Vendor Name], 
    VS.[Vendor No_] AS Vendor_No,
    VS.[Purchase_Date],
    VS.Contracted_Quantity,
    RQ.Remaining_Qty,
    CASE 
        WHEN VS.[Contract Type] = 1 THEN 'CONTRACT A'
        WHEN VS.[Contract Type] = 2 THEN 'CONTRACT B'
        ELSE 'OTHERS'
    END AS [Contract Type],
    -- 消耗月份作为透视维度，无消耗的月份留空
    CQ.Consumed_Date,
    CQ.Consumed_Qty * -1 AS Consumed_Qty
FROM
(
    SELECT 
        [Lot No_],
        [Vendor Name],
        [Vendor No_],
        [Contract Type],
        FORMAT([Date Created], 'yyyy-MMM') AS Purchase_Date,
        -- 按批次汇总合同数量，避免后续关联重复
        SUM(Quantity) AS Contracted_Quantity
    FROM [A].[dbo].[Company$Volume Summary]
    GROUP BY [Lot No_], [Vendor Name], [Vendor No_], [Contract Type], FORMAT([Date Created], 'yyyy-MMM')
) VS
-- 关联批次剩余量
LEFT JOIN (
    SELECT [Lot No_] ,SUM([Remaining Quantity]) AS Remaining_Qty 
    FROM [A].[dbo].[Company$Item Ledger Entry]
    GROUP BY [Lot No_]
) RQ ON RQ.[Lot No_] = VS.[Lot No_]
-- 关联批次各月消耗，用数值型年月判断大小避免字符串比较问题
LEFT JOIN (
    SELECT 
        [Lot No_],
        FORMAT([Posting Date], 'yyyy-MMM') AS Consumed_Date,
        -- 转为yyyyMM数值型用于比较
        CAST(FORMAT([Posting Date], 'yyyyMM') AS INT) AS Consumed_YM,
        SUM([Quantity]) AS Consumed_Qty
    FROM [A].[dbo].[Company$Item Ledger Entry]
    WHERE [Entry Type] = '5' 
    GROUP BY [Lot No_], FORMAT([Posting Date], 'yyyy-MMM'), CAST(FORMAT([Posting Date], 'yyyyMM') AS INT)
) CQ ON CQ.[Lot No_] = VS.[Lot No_] 
    AND CQ.Consumed_YM >= CAST(FORMAT(VS.Purchase_Date + '01', 'yyyyMM') AS INT)
) cs 
PIVOT
(
    SUM(Consumed_Qty)
    -- 按消耗月份透视，对应列展示对应月份的消耗
    FOR Consumed_Date IN ([2021-Sep], [2021-Oct], [2021-Nov])
) pvt

修改说明 #

1. 拆分了采购月份和消耗月份两个独立字段，用消耗月份作为透视维度，保证各月消耗数据可以落在对应列中。
2. 新增yyyyMM格式的整数年月用于日期大小比较，彻底解决字符串比较的逻辑错误。
3. 提前按批次聚合采购数量，避免关联多月份消耗数据后重复计算。
4. 修复了GROUP BY中的无效别名PT的笔误。

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