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.

Answer 2

This will work as long as there are less than 2047 days between from and to dates

declare @from smalldatetime = '10/01/2012'
declare @to smalldatetime = '10/15/2012'

select t.id, dateadd(day, number,@from), isnull(val, 0) val from @temp t
right join master..spt_values s
on dateadd(d, s.number, @from) = t.CDate
where
datediff(day, @from, @to ) > s.number
and s.type = 'P'

Answer 3

I think the best way to do this is to create your own table with dates (you can also use master.dbo.spt_values, but I personally don't like that solution)

declare @Temp_Dates table (CDate datetime)
declare @Date datetime
select @Date = (select min(CDate) from temp)

while @Date <= (select max(CDate) from temp)
begin
    insert into @Temp_Dates (CDate)
    select @Date

    select @Date = dateadd(dd, 1, @Date)
end

select D.CDate, isnull(T.id, 0) as id
from @Temp_Dates as D
    left outer join temp as T on T.CDate = D.CDate

you can also use recursive solution with CTE