generate list of dates in sql server

Sometimes, you may come across the situation where you have to generate a list of date range, given start date and end date. So, here is the simple script using 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

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', '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 a 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.

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 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