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

Difference between Char and Varchar

Recently, one of my colleague asked me the difference between the datatypes char, varchar, nchar and nvarchar in SQL Server.  Also, in the past I have seen the same question being asked in interviews. So, i decided to write a post on the same.

char(n): takes exact "n" bytes regardless of the data you store in it (with trailing spaces, if the data length is less than "n").

varchar(n): takes "x" no. of bytes depending on the data + 2 bytes to store the length of the data

nchar(n) and nvarchar(n) are exactly same as char and varchar respectively but it takes exact double spaces to support multilingual language, 1 byte to store Unicode character for each character

Now question is where to use char over varchar?
Use char dataype only for fixed length column, means, when you know in advance that your data will always be of fixed length.
For example Phone Number, Country code

declare @Ph_No CHAR(10)
declare @Country_Code CHAR(3)

Now, one more question comes to mind even for fixed length columns, what is the difference char(10) and varchar(10), when both can store 10 bytes of data?
char would take 10 bytes while varchar 10+2 bytes
Select operation is fast with char columns as compare to varchar
Sample code for reference

declare @char char(5)
set @char = 'ABC' -- 5 bytes
set @char = 'ABCDE' -- 5 bytes

declare @varchar varchar(5)
set @varchar = 'ABC' -- 3 + 2 = 5 bytes
set @varchar = 'ABCDE' -- 5 + 2 = 7 bytes

declare @nchar nchar(5)
set @nchar = 'ABC' -- 5*2 = 10 bytes
set @nchar = 'ABCDE' -- 5*2 = 10 bytes

declare @nvarchar nvarchar(5)
set @nvarchar = 'ABC' -- 3*2+2 = 8 bytes
set @nvarchar = 'ABCDE' -- 5*2+2 = 12 bytes

Generate List of dates in SQL Server

Generating list of dates is very common requirement in reporting. In this post, we will learn how to generate a list of date range for the given start date and end date.

Below is the simple script using recursive CTE to generate the list of dates

declare @date_from datetime, @date_to datetime
set @date_from = '11/01/2012'
set @date_to = '11/10/2012'
;with dates as(
    select @date_from as dt
    union all
    select DATEADD(d,1,dt) from dates where dt<@date_to
)
select * from dates

OUTPUT

Now, let's take a scenario where it is required to generate range of dates.

Suppose, there is a table "sales" that contains sale data

Lets create a Sales Table and populate some data in it.

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

Now, lets assume using the above data, you have to generate sale report to get total quantity of a product sold each day. Now the problem is, there is no data for some days between the start and end date, so you would not be getting all the dates in the result, but the requirement is all dates should appear in the result with zero quantity even if there is no sale.

declare @date_from datetime, @date_to datetime
set @date_from = '11/01/2012'
set @date_to = '11/10/2012'
select   sale_date, SUM(qty) as total_qty
from     sales
where    sale_date between @date_from and @date_to
group by sale_date
order by sale_date

OUTPUT

Now, in order to generate all the dates in the sale report, first we are generating a list of all dates between start and end date using recursive CTE and then using Left Join with the existing query to display all the dates in the result.

declare @date_from datetime, @date_to datetime
set @date_from = '11/01/2012'
set @date_to = '11/10/2012'
;with dates as(
    select @date_from as dt
    union all
    select DATEADD(d,1,dt) from dates where dt<@date_to
)
select  d.dt, isnull(total_qty,0) as total_qty
from    dates d 
left join (
    select   sale_date, SUM(qty) as total_qty
    from     sales
    where    sale_date between @date_from and @date_to
    group by sale_date
) s on d.dt = s.sale_date
order by d.dt

OUTPUT

Related posts:

• Generate Weekdays in SQL Server
• Generate List of Months in SQL Server