SQL Server: calculate all days per month when no orders?

Question

Suppose you have orders

   | Order |     Date    | 
   -----------------------
   |   1   |   20150101  |
   |   2   |   20150103  |
   |   3   |   20150105  |
   |   11  |   20150211  |
   |   22  |   20150224  |
   |   33  |   20150204  |

and the days when no orders are

   |  Month  | NoOrdersCount |
   ---------------------------   
   | 201501  |      28       |
   | 201502  |      25       |

I want to do this in T-SQL. My first idea is to create table for a year where all days per line and left join this with the orders. Now the sum of NULLs gives us the results of no orders.

How can you calculate the number of NoOrders per month from transaction data like the above in SQL Server 2014?

Answer 1

You don't need an additional table. All you need is a way to calculate the number of days in a month. Here is one method:

select year(date), month(date),
       (datediff(day,
                 dateadd(day, 1 - day(min(date)), min(date),
                 eomonth(date) + 1 -
        count(distinct date)
       ) as NoOrdersCount
from dates
group by year(date), month(date);

As a note: If you know you will always have one day in the month that is not the 29th, 30th, or 31st, you can use:

select year(date), month(date),
       (datediff(day, min(date), dateadd(month, 1, min(date))) -
        count(distinct date)
       ) as NoOrdersCount
from dates
group by year(date), month(date);