There are not 59 minutes and 59 seconds in hour #1. There isn't even 59 minutes, 59.9999 seconds. There is 60 seconds in a minute and 60 minutes in an hour. Those are constants.
If you were counting 2:00:00 as being part of hour #1, there would be 60 minutes and 1 second in hour #1.
Test it for yourself. Count from 1:00:00 to 2:00:00 incrementing by 1 second.
Or just run the following:
Or if that doesn't convince you, run the following. You will see that if you included 2:00 as part of hour #1, it would be the 61st minute, not the 60th.
I didn't read the whole article, but had a couple on the more basic points.
SELECTCOUNT(*)FROM[MY_EVENTS]WHERE---CASES 3, 4 AND 5(([STARTING_TIME] > 7/1/2007 12:00 PM AND [STARTING_TIME] < 7/1/2007 1:00 PM)OR ([ENDING_TIME] > 7/1/2007 12:00 PM AND [ENDING_TIME] < 7/1/2007 1:00 PM))OR---CASE 2([STARTING_TIME] < 7/1/2007 12:00 PM AND [ENDING_TIME] > 7/1/2007 1:00 PM)
This could be written much more simply as
SELECTCOUNT(*)FROM[MY_EVENTS]WHERE [STARTING_TIME] <= 7/1/2007 1:00 PMAND [ENDING_TIME] >= 7/1/2007 12:00 PM --This assumes that starting_time<ending_time
Also to get the time period utilised for each row you could do something like
(((7/1/2007 1:00PM)-(START_TIME)-(CASE END_TIME<(7/1/2007 1:00PM) THEN ((7/1/2007 1:00PM)-(END_TIME)) ELSE 0))%60)*24*60
to get the number of minutes
Not criticising, it's just what I thought of what I began, and couldn't continue until I wrote it down
What about the situation where the Start date/times match? What about the situation where the Start and End date/times match? How does this affect the WHERE clause of the first query?