The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
How to generate missing dates in SELECT?
Posted: 02 Dec 08 12:31 PM
|
I am using the history table which has data for many users who may or may not have history entered for a particular day.
What I need to do is show by user ALL weekday 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 days that don't have history. 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. |
|
|
| |
|
Re: How to generate missing dates in SELECT?
Posted: 02 Dec 08 2:01 PM
|
This common problem is a nasty one. Here are a couple of approaches:
1. Create a table that contains a record for each work day. This can act as your base table to ensure you get a result for each day
2. If the report is always run by week, create a sub report for each work day (5 I suppose). Your main report will need to pass the date range and userid to each sub report
Neither of these are ideal. Reporting on non existent data is a real pain.
Timmus |
|
|
|
Re: How to generate missing dates in SELECT?
Posted: 02 Dec 08 2:04 PM
|
Timmus,
I suppose we were posting at the same time. Read my reply right before yours about Tally tables. This is the answer I was looking for and I think it will be for you too.
John G. |
|
|
| |
|