查询近60天无使用记录的物品的SQL语句问题

原查询的核心问题是提前用WHERE过滤了旧交易记录——如果某个物品同时存在旧记录和近60天内的活动记录，旧记录会被保留并参与分组，导致本应排除的活跃物品被错误返回。正确逻辑应该是先计算每个物品的最后活动日期，再筛选最后日期早于60天前的物品。

修正后的SQL查询 #

SELECT 
    il.BranchID,
    il.ItemID,
    i.Description1,
    il.MostRecentDate,
    'ord.pick' AS TransactionType,
    iv.Quantity
FROM (
    -- 先计算每个分支+物品的最近'ord.pick'活动日期
    SELECT 
        BranchID,
        ItemID,
        MAX(LedgerDate) AS MostRecentDate
    FROM [Latitude].[dbo].[ItemLedger]
    WHERE 
        TransactionType IN ('ord.pick')
        AND BranchID IN ('Waverly','Mattoon')
    GROUP BY BranchID, ItemID
) il
-- 关联库存表时需匹配分支（假设库存按分支管理）
JOIN Inventory iv 
    ON il.ItemID = iv.ItemID 
    AND il.BranchID = iv.BranchID
JOIN Item i 
    ON il.ItemID = i.ItemID
WHERE 
    -- 筛选最后活动日期早于60天前的物品
    il.MostRecentDate < DATEADD(day, -60, CAST(GETDATE() AS DATE))
    AND iv.Quantity > 0 -- 去掉字符串引号，用数值比较
    AND i.ItemID <> '<Generic>'
ORDER BY iv.Quantity DESC

关键修正点 #

1. 先聚合再筛选：通过子查询先计算每个物品的最后活动日期，再过滤符合条件的物品，避免提前过滤记录导致的错误。
2. 修复库存数量比较：原查询中iv.Quantity > '0'是字符串比较，改为数值比较iv.Quantity > 0，避免类型转换异常。
3. 关联库存时匹配分支：如果库存是按分支管理的，必须关联BranchID才能获取对应分支的正确库存，否则会出现跨分支的错误关联。
4. 简化交易类型显示：因为只关注ord.pick类型，直接在SELECT中指定该值，无需在GROUP BY中冗余包含。

扩展：包含从未有过活动的物品 #

如果需要同时找出从未产生过ord.pick活动的物品（也符合"近60天无活动"的需求），可以改用LEFT JOIN实现：

SELECT 
    iv.BranchID,
    iv.ItemID,
    i.Description1,
    NULL AS MostRecentDate,
    'ord.pick' AS TransactionType,
    iv.Quantity
FROM Inventory iv
JOIN Item i 
    ON iv.ItemID = i.ItemID
LEFT JOIN (
    SELECT 
        BranchID,
        ItemID,
        MAX(LedgerDate) AS MostRecentDate
    FROM [Latitude].[dbo].[ItemLedger]
    WHERE 
        TransactionType IN ('ord.pick')
        AND BranchID IN ('Waverly','Mattoon')
    GROUP BY BranchID, ItemID
) il
    ON iv.ItemID = il.ItemID 
    AND iv.BranchID = il.BranchID
WHERE 
    (il.MostRecentDate IS NULL OR il.MostRecentDate < DATEADD(day, -60, CAST(GETDATE() AS DATE)))
    AND iv.Quantity > 0
    AND i.ItemID <> '<Generic>'
    AND iv.BranchID IN ('Waverly','Mattoon')
ORDER BY iv.Quantity DESC

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