Calculating Hours With Overlapping Times

All,

I have a table with start and end dates/times in it, and would like to
be able to calculate the number of hours represented, accounting for
overlapping records.

Note that I am looking for an answer on HOW to do this--I don't
necessarily need it to be written for me (although it would not go
unappreciated!).


CREATE TABLE [dbo].[session_temp] (
[session_pk] [int] IDENTITY (1, 1) NOT NULL ,
[date_start] [smalldatetime] NULL ,
[date_end] [smalldatetime] NULL
) ON [PRIMARY]
GO

--These values make a very simplistic example,
--as they only represent one
--session,so using min and max would work in this case,
--but would not work if there
--were multiple sessions involved.

--hopefully you get the idea of what I am going for:

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10:30pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10:45pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 11pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/5/04 2am')

--the query I am looking to write would return "5"
--the one below obviously does not do what I am looking for
SELECT
SUM(CAST(DATEDIFF(ss,date_start,date_end) AS NUMERIC(8,2))/3600)
FROM
session_temp



Thanks very much for any insight.

Phil

---
Check out my poker-only weblog at:
http://www.livejournal.com/users/chicago_phil/
Download my session-tracking spreadsheet at:
http://www.geocities.com/fibby70/