Incidentally, if you're using SQL 2005, you can jump straight to the final result fairly elegantly.

Code 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';

--e) output is 4 rows

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,

            DATEDIFF(mi,

            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, 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

*/