T-SQL Calculating time stats when StartTime and EndTime span different months

Question

I am querying a database with records of meeting room reservations. Since we are a global company we have meeting reservations that span different months, example:

StartTime ........................ EndTime........................ MeetingName

06/30/2010 11:45PM ........ 07/01/2010 01:00AM ..... My Meeting

If I want to determine utilization for July 2010, how would I include the hour that a room was utilized in the above reservation? If my report is for only hours utilized within July.

And same holds true for if I was reporting on June...how would I only report on the 45 minutes that the meeting used in June?

If you need more information, let me know.

Answer 1

Since your database isn't standardized on time, then you will need to determine the timezone for each record, or, if possible, org unit, and then you can do your work with everything in the same time zone.