How to generate missing dates in SELECT?

Question

Post reply

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: 42179 More actions · Answer 1

Chris Harshman

SSC-Forever

Points: 42179

December 2, 2008 at 11:25 am

#906418

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

John Gundrum

SSC-Addicted

Points: 427

December 2, 2008 at 11:32 am

#906423

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: 42179 More actions · Answer 3

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

http://qa.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.