我目前正在按月分组和汇总库存使用情况：
SELECT Inventory.itemid AS ItemID,SUM(Inventory.Totalunits) AS Individual_MonthQty,MONTH(Inventory.dadded) AS Individual_MonthAsNumber,DATENAME(MONTH,Inventory.dadded) AS Individual_MonthAsString FROM Inventory WHERE Inventory.invtype = 'Shipment' AND Inventory.dadded >= @StartRange AND Inventory.dadded <= @EndRange GROUP BY Inventory.ItemID,MONTH(Inventory.dadded),Inventory.dadded)
这给了我期待的结果：
ItemID Kit_MonthQty Kit_MonthAsNumber Kit_MonthAsString 13188 234 8 August 13188 45 9 September 13188 61 10 October 13188 20 12 December
题
在没有数据存在的月份,我必须做什么才能返回零,如下所示：
ItemID Kit_MonthQty Kit_MonthAsNumber Kit_MonthAsString 13188 0 1 January 13188 0 2 February 13188 0 3 March 13188 0 4 April 13188 0 5 May 13188 0 6 June 13188 0 7 July 13188 234 8 August 13188 45 9 September 13188 61 10 October 13188 0 11 November 13188 20 12 December
解决方法
在过去,我通过创建一个临时表来解决这样的问题,该表将保存所需的所有日期：
CREATE TABLE #AllDates (ThisDate datetime null)
SET @CurrentDate = @StartRange
-- insert all dates into temp table
WHILE @CurrentDate <= @EndRange
BEGIN
INSERT INTO #AllDates values(@CurrentDate)
SET @CurrentDate = dateadd(mm,1,@CurrentDate)
END
SELECT ALLItems.ItemId,SUM(COALESCE(Inventory.Qty,0)) AS Individual_MonthQty,MONTH(#AllDates.ThisDate) AS Individual_MonthAsNumber,#AllDates.ThisDate) AS Individual_MonthAsString
FROM #AllDates
JOIN (SELECT DISTINCT dbo.Inventory.ItemId FROM dbo.Inventory) AS ALLItems ON 1 = 1
LEFT JOIN Inventory ON DATEADD(dd,- DAY(Inventory.dadded) +1,Inventory.dadded) = #AllDates.ThisDate AND ALLItems.ItemId = dbo.Inventory.ItemId
WHERE
#AllDates.ThisDate >= @StartRange
AND #AllDates.ThisDate <= @EndRange
GROUP BY ALLItems.ItemId,#AllDates.ThisDate
那么你应该有一个每个月的记录,无论它是否存在于库存中.
