sql missing rows when grouped by DAY, MONTH, YEAR

Question

If I select from a table group by the month, day, year, it only returns rows with records and leaves out combinations without any records, making it appear at a glance that every day or month has activity, you have to look at the date column actively for gaps. How can I get a row for every day/month/year, even when no data is present, in T-SQL?

Answer 1

Create a calendar table and outer join on that table

Answer 2

Look into using a numbers table. While it can be hackish, it's the best method I've come by to quickly query missing data, or show all dates, or anything where you want to examine values within a range, regardless of whether all values in that range are used.

Answer 3

Building on what SQLMenace said, you can use a CROSS JOIN to quickly populate the table or efficiently create it in memory.
http://www.sitepoint.com/forums/showthread.php?t=562806

Answer 4

My developer got back to me with this code, underscores converted to dashes because StackOverflow was mangling underscores -- no numbers table required. Our example is complicated a bit by a join to another table, but maybe the code example will help someone someday.

declare @career-fair-id int 
select @career-fair-id = 125

create table #data ([date] datetime null, [cumulative] int null) 

declare @event-date datetime, @current-process-date datetime, @day-count int 
select @event-date = (select careerfairdate from tbl-career-fair where careerfairid = @career-fair-id) 
select @current-process-date = dateadd(day, -90, @event-date) 

 while @event-date <> @current-process-date 
 begin 
 select @current-process-date = dateadd(day, 1, @current-process-date) 
 select @day-count = (select count(*) from tbl-career-fair-junction where attendanceregister <= @current-process-date and careerfairid = @career-fair-id) 
  if @current-process-date <= getdate() 
     insert into #data ([date], [cumulative]) values(@current-process-date, @day-count) 
 end 

 select * from #data 
 drop table #data