Include missing months in Group By query

Question

I think I have a tough one here... :(

I am trying to get an order count by month, even when zero. Here's the problem query:

SELECT datename(month, OrderDate) as Month, COUNT(OrderNumber) AS Orders
FROM OrderTable
WHERE OrderDate >= '2012-01-01' and OrderDate <= '2012-06-30'
GROUP BY year(OrderDate), month(OrderDate), datename(month, OrderDate)

What I'm looking to get is something like this:

Month            Orders
-----            ------
January          10
February         7
March            0
April            12
May              0
June             5

...but my query skips a row for March and May. I've tried COALESCE(COUNT(OrderNumber), 0) and ISNULL(COUNT(OrderNumber), 0) but I'm pretty sure the grouping is causing that not to work.

Solution 1

This solution doesn't require you to hard-code the list of months you might want, all you need to do is provide any start date and any end date, and it will calculate the month boundaries for you. It includes year in the output so that it will support more than 12 months and so that your start and end dates can cross a year boundary and still order correctly and show the correct month and year.

DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;

SELECT @StartDate = '20120101', @EndDate = '20120630';

;WITH d(d) AS 
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
    FROM sys.all_objects ORDER BY [object_id] ) AS n
)
SELECT 
  [Month]    = DATENAME(MONTH, d.d), 
  [Year]     = YEAR(d.d), 
  OrderCount = COUNT(o.OrderNumber) 
FROM d LEFT OUTER JOIN dbo.OrderTable AS o
  ON o.OrderDate >= d.d
  AND o.OrderDate < DATEADD(MONTH, 1, d.d)
GROUP BY d.d
ORDER BY d.d;

Solution 2

Since your query Just Can't guess the months you want, you will need to have the Months that you want stored in somewhere, Join them with your table, and then group. Something like:

;With Months (Month) 
AS
(

    select 'January' as Month
    UNION
    select 'February' as Month
    UNION
    select 'March' as Month
    UNION
    select 'April' as Month
    UNION
    select 'May' as Month
    UNION
    select 'June' as Month
    UNION
    select 'July' as Month
    UNION
    select 'August' as Month
    UNION
    select 'September' as Month
    UNION
    select 'October' as Month
    UNION
    select 'November' as Month
    UNION
    select 'December' as Month

)
--Also you could have them in a "Months" Table

Then Just JOIN this table with your table:

   Select 
    SELECT datename(month, OrderDate) as Month, COUNT(OrderNumber) 
    FROM Months T1
    LEFT JOIN OrderTable T2 on datename(month, T2.OrderDate) = T2.Month
    WHERE (T2.OrderDate >= '2012-01-01' and T2.OrderDate <= '2012-06-30') 
OR T2.OrderDate IS NULL --So will show you the months with no rows
    GROUP BY year(T2.OrderDate), month(T2.OrderDate), datename(month, T2.OrderDate)

Hope it works!

Solution 3

Here is one using recursive CTE:

declare @StartDate datetime = '2015-04-01';
declare @EndDate datetime = '2015-06-01';

-- sample data
declare @orders table (OrderNumber int, OrderDate datetime);
insert into @orders
select 11, '2015-04-02'
union all
select 12, '2015-04-03'
union all
select 13, '2015-05-03'
;

-- recursive CTE
with dates
as (
    select @StartDate as reportMonth
    union all
    select dateadd(m, 1, reportMonth)
    from dates
    where reportMonth < @EndDate
    )
select 
    reportMonth,
    Count = count(o.OrderNumber)
from dates
left outer join @orders as o 
    on o.OrderDate >= reportMonth
    and o.OrderDate < dateadd(MONTH, 1, reportMonth)
group by 
    reportMonth
option (maxrecursion 0);
;

Comments

[andy holaday]

It looks like OrderTable has no records for March or May, so selecting from this table alone can not return a result for those months. Possible work-arounds: If your database has a master table of dates you can leverage that, or you can create your own table of dates on the fly.

[Ta01]

Like @andyholaday said - create a lookup table with January...December and do a Left join, and you will get the missing months.

[Michael Nelson]

Thanks for your effort, but this didn't work for me. I was thinking T1 and T2 were switched around, but I couldn't get it. I don't see how it could work anyway because there still are no results from from OrderTable to join the months table to. If I understand the concept, at best I would get all 12 months, which isn't what I'm looking for either.

[Michael Nelson]

Wow, impressive! Your SELECT didn't return the correct order count, but your WITH part worked great... so I just LEFT JOINed the results of the WITH with my SELECT as a subquery and it works great, year and all!

[Enzero]

Thank you so much this is just what I needed its a great piece of code.

[Baby]

how is this query looks in LINQ? this is really a nightmare to me T_T

[Baby]

possible, but my team hate stored procedure for no reason. But its okay. of I've figured another way.