How to generate missing dates in SELECT?

Question

How to generate missing dates in SELECT?

John Gundrum

SSC-Addicted

Points: 427
More actions
December 2, 2008 at 9:57 am

#128324

I have a table of data that includes a date field showing when a task was completed. There is data for many users who may or may not have entered data for a particular day.
What I need to do is show by user ALL dates and the number of activities the user has completed. The problem is showing dates where no tasks have been completed. I need to show 0 for that day. As it is, the date is missing and averages are not being calculated properly on the report.
Is there a way to include all weekday dates using a SELECT even when there is no data for that date?
If more explanation is needed please let me know.
Thanks,
John G.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

Have you ever worked with a tally table before? I think using that, and left outer joining to a subquery where you count the activities per day. To explain this better, I'd probably need to see your original query.

John Gundrum SSC-Addicted Points: 427 More actions · Answer 2

Chris,

No, I have never worked with a tally table. Can you explain what it is?

Is it possibly a table of all possible dates?

Thanks,

John G.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

Probably a better explanation of it than I could give is in this article:

http://www.sqlservercentral.com/articles/TSQL/62867/

You could set up your tally table in a couple of different ways, but if possible I'd recommend just 1 column with whole numbers from 1 to whatever, depending on how much data you're reporting at a time. (a week? a month? a year?)

If you're reporting for a week, you could use DATEPART(weekday, somedatecolumn) to get a number from 1 to 7. If you're reporting for a month, you could use DATEPART(day, somedatecolumn) to get the day of the month, etc.

That approach might be more robust than trying to create a table with all the dates in it, and remembering to update it whenever you get close to the end.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Here is a link to an article that talks about the Tally Table: http://www.sqlservercentral.com/articles/TSQL/62867/

John Gundrum SSC-Addicted Points: 427 More actions · Answer 5

Chris,

Just finished reading the Tally table article and it seems to be exactly what I was looking for!

Thanks alot!

John G.