SQL Group and Sum By Month - Default to Zero

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                  N开发者_JS百科ovember
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.

You could prepare a 'calendar' table like so:

DECLARE @d datetime
SET @d = @StartRange

DECLARE @calendar TABLE (Date datetime)

WHILE (@d <= @EndRange) BEGIN
    INSERT INTO @Calendar VALUES (@d)
    SET @d = DATEADD(month, 1, @d)
END

and then do a LEFT JOIN with your table on the month and year date parts. This way you'll always have all the months between the start and end date as rows.

Here's one way. This assumes that @StartRange and @EndRange fall within the same calendar year, otherwise you will get some funny results.

WITH n(n) AS 
(
    SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.objects
),
months(m, mn) AS
(
    SELECT n, DATENAME(MONTH, DATEADD(MONTH, m-1, '19000101')) 
        FROM n
)
SELECT 
    ItemID = i.itemid,
    MonthQty = COALESCE(SUM(TotalUnits), 0),
    MonthAsNumber = m.m,
    MonthAsString = m.mn
FROM
    months AS m
LEFT OUTER JOIN
    Inventory AS i
ON
    MONTH(i.dadded) = m.m
WHERE
    i.invtype = 'Shipment'
    AND i.dadded >= @StartRange
    AND i.dadded <= @EndRange
GROUP BY
    m.m, m.mn, i.itemid
ORDER BY m.m;