Nov 26, 2012

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

    Choose :
  • OR
  • To comment
2 comments:
Write Comments
  1. Hai,
    the Above code Shown is very good.
    But Can elaborate the code using Nested Group by function. Like first group the prod_code , then group the cust_code and then finally group by date.

    ReplyDelete
    Replies
    1. Dear
      Yes, the code can be elaborated using "group by" as suggested. The basic idea of the post is to share, how to generate list of all the months when data is not present for all months.

      Delete