Show all dates data between two dates, if no row exists for particular date then show zero in all columns

Try This :) replace ActivitiesId with your table Id and Activities table with your table name.

Declare @start nvarchar(max) set @start = '07/10/2013'
Declare @end nvarchar(max) set @end = '07/20/2013'

;with d(date) as (
  select cast(@start as datetime)
  union all
  select date+1
  from d
  where date < @end
select isnull(t.ActivitiesId,0) as ActivitiesId , CreatedOn
from d

left join Activities t on CAST(Floor(CAST(t.CreatedOn as Float)) as datetime) =  and t.UserId = 14


Post a Comment

Related Posts Plugin for WordPress, Blogger...