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
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
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
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.
You can also refer related posts to generate list of dates and weekdays
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 12Result
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.mthResult
You can also refer related posts to generate list of dates and weekdays