>I've been asked to create a query that returns room usage by the hour. I feel bad about asking just as I'm starting, but it is one of those by the end of the day request.
>
>I have a table of scheduled events that has the roomID, BeginTIme and Endtime (these are datetime fields). The kicker part is I have to return 0's for time when rooms are not "normally" in use. So the 0100, 0200 etc would have to be returned as well as the hours between 0700-1800.
>
>Thanks for any help.
>
>Kirk
If you create a table with entries like
BeginTime EndTime
0100 0200
0200 0300
etc.
and use this table to left join with your scheduled events table, can you get the result you want?
If it's not broken, fix it until it is.
My Blog