SQL组和按月计算总和 - 默认为零 [英] SQL Group and Sum By Month - Default to Zero

问题描述

我目前按月分组和总结库存使用情况：

  SELECT Inventory.itemid AS ItemID，
 SUM （Inventory.Totalunits）AS Individual_MonthQty，
个月（Inventory.dadded）AS Individual_MonthAsNumber，
 DATENAME（月，Inventory.dadded）AS Individual_MonthAsString 
从存货
其中Inventory.invtype = 'Shipment'
 AND Inventory.dadded> = @StartRange 
 AND Inventory.dadded< = @EndRange 
 GROUP BY Inventory.ItemID，
 MONTH（Inventory.dadded）， 
 DATENAME（MONTH，Inventory.dadded）
  

这给我的结果是我期待：

 的ItemID Kit_MonthQty Kit_MonthAsNumber Kit_MonthAsString 
 13188 234 8月8日
 13188 45 9月9日
 13188 61 10 Octobe r 
 13188 20 12月12日
  

问题

在没有数据存在的月份，我必须做些什么才能返回零，如下所示：

 的ItemID Kit_MonthQty Kit_MonthAsNumber Kit_MonthAsString 
 13188 0 1月1日
 13188 0 2月2日
 13188 0 3月3日
 13188 0 4月4日
 13188 0 5月5月
 13188 0 6月6日
 13188 0 7月7日
 13188 234 8月8日
 13188 45 9月9日
 13188 61 10月10日
 13188 0 11 11月
 13188 20 12月12日
  
 pre $ code CREATE TABLE #AllDates（ThisDate datetime null）
 SET @CurrentDate = @StartRange 
 
  - 将所有日期插入临时表
 WHILE @CurrentDate< = @EndRange 
 BEGIN 
 INSERT INTO #AllDates值（@CurrentDate）
 SET @CurrentDate = dateadd（mm，1，@CurrentDate）
 END 
  

然后，修改你的查询加入这张表：

$ $ p $ SELECT ALLItems.ItemId，
SUM（COALESCE（Inventory.Qty，0） ）AS Individual_MonthQty，
MONTH（＃AllDates.ThisDate）AS Individual_MonthAsNumber，
DATENAME（MONTH，＃AllDates.ThisDate）AS Individual_MonthAsString
起价#AllDates
JOIN（SELECT DISTINCT DBO。 Inventory.ItemI d FROM Dbo.Inventory）作为ALLItems ON 1 = 1
LEFT JOIN存货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


然后，您应该每个月都有记录，无论它是否存在于广告资源中。

I am currently grouping and summing inventory usage by month:

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), 
            DATENAME(MONTH, Inventory.dadded)

This gives me the results that I'm expecting:

ItemID  Kit_MonthQty    Kit_MonthAsNumber   Kit_MonthAsString
13188   234             8                   August
13188   45              9                   September
13188   61              10                  October
13188   20              12                  December

Question

What must I do to return zero for months where no data exsits, like this:

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

解决方案

In the past, I've solved a problem like this by creating a temporary table which will hold all dates needed:

    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

Then, modify your query to join against this table:

SELECT      ALLItems.ItemId,
            SUM(COALESCE(Inventory.Qty, 0)) AS Individual_MonthQty,
            MONTH(#AllDates.ThisDate) AS Individual_MonthAsNumber,
            DATENAME(MONTH, #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

Then you should have a record for each month, regardless of whether it exists in Inventory.

这篇关于SQL组和按月计算总和 - 默认为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！