448,956 Members | 1,224 Online
Need help? Post your question and get tips & solutions from a community of 448,956 IT Pros & Developers. It's quick & easy.

# Calculating Hours with Overlapping Times

 P: n/a 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/ Jul 20 '05 #1