Hi all,
I'd approach it more like this, I think. The code below produces the same results and is safe to run...
DECLARE @MY_EVENTS TABLE (DESCRIPTION VARCHAR(50), STARTING_TIME DATETIME, ENDING_TIME DATETIME)
INSERT @MY_EVENTS
SELECT 'James Bond', '20070701 10:30', '20070701 11:00'
UNION SELECT 'Sally May', '20070701 11:30', '20070701 13:30'
UNION SELECT 'Jerry Jones', '20070701 11:30', '20070701 12:30'
UNION SELECT 'Mike Moxie', '20070701 12:30', '20070701 13:00'
UNION SELECT 'Carl Cluff', '20070701 12:30', '20070701 14:00'
UNION SELECT 'Mad Max', '20070701 14:00', '20070701 14:30'
--a) output is 4
SELECT COUNT(*) FROM @MY_EVENTS
WHERE NOT (ENDING_TIME < '20070701 12:00' OR STARTING_TIME > '20070701 13:00')
--b) output is 150
SELECT SUM(
DATEDIFF(mi,
CASE WHEN '20070701 12:00' < STARTING_TIME THEN STARTING_TIME ELSE '20070701 12:00' END,
CASE WHEN '20070701 13:00' > ENDING_TIME THEN ENDING_TIME ELSE '20070701 13:00' END))
FROM @MY_EVENTS
--aside
DECLARE @INTEGERS TABLE (i INT) --It's generally recommended you have a static 'numbers' table somewhere - this is for demo only
INSERT @INTEGERS SELECT DISTINCT colid FROM syscolumns WHERE colid BETWEEN 1 AND 24 ORDER BY colid
--c) output is 10 rows
DECLARE @DAYHOURS TABLE (DESCRIPTION VARCHAR(50), STARTING_TIME SMALLDATETIME, BEGIN_HOUR DATETIME, IDURATION INT)
INSERT @DAYHOURS
SELECT DESCRIPTION, STARTING_TIME, BEGIN_HOUR,
CASE WHEN BEGIN_HOUR < STARTING_TIME THEN STARTING_TIME ELSE BEGIN_HOUR END,
CASE WHEN END_HOUR > ENDING_TIME THEN ENDING_TIME ELSE END_HOUR END) AS IDURATION
FROM @MY_EVENTS, (SELECT DATEADD(hh, i-1, '20070701') BEGIN_HOUR, DATEADD(hh, i, '20070701') END_HOUR FROM @Integers) HOURS
WHERE NOT (ENDING_TIME <= BEGIN_HOUR OR STARTING_TIME >= END_HOUR)
SELECT * FROM @DAYHOURS
--d) output is '12:00'
DECLARE @MAXHOUR DATETIME
SET @MAXHOUR = (SELECT TOP 1 BEGIN_HOUR FROM @DAYHOURS GROUP BY BEGIN_HOUR ORDER BY COUNT(*) DESC)
SELECT @MAXHOUR 'Busiest Hour'
--e) output is 4 rows
SELECT * FROM @DAYHOURS WHERE BEGIN_HOUR = @MAXHOUR
I think that's has beens actually, not wannabes, get your facts straight Mr Schneebaum.
Quite aside from the Imperialist yanks and their impending implosion up into their own cultural vacuum, I thought that was quite a useful article. I do a lot of data work, particularly on scheduling processes, so there's some useful lessons there.
One thing I find quite handy, particularly when shunting data between data sources and making comparisons, is to split the date into mm, dd, yy, mins and stop treating it as a date at all.
Actually the ONLY cursor I've ever implemented has been to solve a similar problem, trying to find the difference between two events - I was getting serious runtime issues doing it Joes way, and cut from 1 minute 30 to 10 seconds using a cursor - won't post the code 'cos it's long and boring....
Incidentally, if you're using SQL 2005, you can jump straight to the final result fairly elegantly.
Code is safe to run...
UNION SELECT 'Mad Max', '20070701 14:00', '20070701 14:30';
WITH
INTEGERS AS (SELECT DISTINCT colid i FROM syscolumns WHERE colid BETWEEN 1 AND 24),
HOURS AS (SELECT DATEADD(hh, i-1, '20070701') BEGIN_HOUR, DATEADD(hh, i, '20070701') END_HOUR FROM INTEGERS),
DAYHOURS AS (
SELECT DESCRIPTION, STARTING_TIME, ENDING_TIME, BEGIN_HOUR,
FROM @MY_EVENTS, HOURS
WHERE NOT (ENDING_TIME <= BEGIN_HOUR OR STARTING_TIME >= END_HOUR))
SELECT * FROM DAYHOURS WHERE BEGIN_HOUR = (SELECT TOP 1 BEGIN_HOUR FROM DAYHOURS GROUP BY BEGIN_HOUR ORDER BY COUNT(*) DESC)
/*
DESCRIPTION STARTING_TIME ENDING_TIME BEGIN_HOUR IDURATION
-------------------- ----------------------- ----------------------- ----------------------- -----------
Carl Cluff 2007-07-01 12:30:00.000 2007-07-01 14:00:00.000 2007-07-01 12:00:00.000 30
Jerry Jones 2007-07-01 11:30:00.000 2007-07-01 12:30:00.000 2007-07-01 12:00:00.000 30
Mike Moxie 2007-07-01 12:30:00.000 2007-07-01 13:00:00.000 2007-07-01 12:00:00.000 30
Sally May 2007-07-01 11:30:00.000 2007-07-01 13:30:00.000 2007-07-01 12:00:00.000 60
*/
Oh, and here are my favo(u)rite date links (so to speak)...
http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
I hope someone finds all this stuff useful...
Why are you defaulting to 59 minutes if the process was running before the hour started and finished after the hour ended?
A process that is already running at 12:00 and is still running at 1:00 was running for 60 minutes from 12:00 to 1:00, not 59 minutes. Likewise, if ti started at 12:30 and was still running at 1:00, then it was running for 30 minutes, not 29 minutes, during that hour.
