sql - SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns

Question

I want to show all dates between two dates when there is any date data missing then its should show zero in val column .

declare @temp table (
id int identity(1,1) not null,
CDate smalldatetime ,
val int
)

INSERT STATEMENT FOR DATA TO CHECK

insert into @temp select '10/2/2012',1
insert into @temp select '10/3/2012',1
insert into @temp select '10/5/2012',1
insert into @temp select '10/7/2012',2
insert into @temp select '10/9/2012',2
insert into @temp select '10/10/2012',2
insert into @temp select '10/13/2012',2
insert into @temp select '10/15/2012',2

Retrieve records between first day of month and today

select * from @temp where CDate between '10/01/2012' AND '10/15/2012'

As i run this query its show me all data between these two dates but i want to also include missing dates with val=0

SQL FIDDLE WITH SAMPLE DATA

Answer 1

;with d(date) as (
  select cast('10/01/2012' as datetime)
  union all
  select date+1
  from d
  where date < '10/15/2012'
  )
select t.ID, d.date CDate, isnull(t.val, 0) val
from d
left join temp t
       on t.CDate = d.date
order by d.date
OPTION (MAXRECURSION 0) -- use this if your dates are >99 days apart

You need to make up the dates, so I've use a recursive common table expression here. SQL Fiddle

MAXRECURSION number

Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.

When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.