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