Generate list of Months in SQL Server

In this article, we will learn how to generate list of months in Sql Server and in which scenario it is required?

Script to generate the list of months

declare @year int
set @year = 2012

select number as mth, DATENAME(MONTH, cast(@year*100+number as varchar) + '01')  as monthname 
from master.dbo.spt_values
where type = 'P' and number between 1 and 12

Result

Now, let's take a scenario where it is required to generate list of months. Suppose, there is a table "sales" that contains sale data Script to create sale table with data

create table sales(
      cust_code varchar(10)
    , prod_code varchar(5)
    , sale_date datetime
    , qty int
)
insert into sales
select 'C001', 'P01', '01/01/2012', 2 union all
select 'C002', 'P01', '02/02/2012', 2 union all
select 'C001', 'P01', '03/03/2012', 5 union all
select 'C002', 'P01', '05/05/2012', 2 union all
select 'C003', 'P01', '05/05/2012', 3

Now, lets assume using the above data, you have to generate a sale report to get total quantity of a product sold each month. Now the problem is, there is no data for some months, so you would not be getting all the months in the result but the requirement is to have all the months with zero quantity

declare @year int
set @year = 2012
select   MONTH(sale_date) as mth, DATENAME(MONTH, sale_date) as monthname, SUM(qty) as total_qty
from     sales
where    YEAR(sale_date) = @year
group by MONTH(sale_date), DATENAME(MONTH, sale_date)
order by MONTH(sale_date)

Result

Now, in order to generate all the months in the sale report, first we are generating a list of all months and then using "Left Join" with the existing query to display all the months in the result.

declare @year int
set @year = 2012

select  m.number as mth, DATENAME(MONTH, cast(@year*100+number as varchar) + '01') as monthname
 , @year as year, isnull(total_qty,0) as total_qty
from (
 select number
 from    master.dbo.spt_values 
 where type = 'P' and number between 1 and 12
) m 
left join (
    select   MONTH(sale_date) as mth,SUM(qty) as total_qty
    from     sales
    where    YEAR(sale_date) = @year
    group by MONTH(sale_date)
) s on m.number = s.mth

Result

You can also refer related posts to generate list of dates and weekdays

• generate list of dates in sql
• generate weekdays in sql