Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Calculating of overlapping Timestamp

Author  Topic  Brico Starting Member 1 Post Posted - 2011-03-28 : 10:58:00 Hi does anybody know hoe to calculate overlapping times. I have been stuck on this for days. I have a timetable for a member of staff DECLARE @Timetable_tb TABLE (AcadPeriod NVARCHAR(5), CC_staffId NVARCHAR(6), week_day INT, eventID NVARCHAR(5), start_time DATETIME, end_Time DATETIME, duration_hrs FLOAT) INSERT INTO @Timetable_tb VALUES( '10/11','001577', 2,'36380','09:00:00','10:00:00', 1); INSERT INTO @Timetable_tb VALUES( '10/11','001577', 2,'36381','09:30:00','10:30:00', 1); INSERT INTO @Timetable_tb VALUES( '10/11','001577', 2,'36383','12:00:00','13:30:00', 1.5); INSERT INTO @Timetable_tb VALUES( '10/11','001577', 2,'36384','09:00:00','09:15:00', 0.25); INSERT INTO @Timetable_tb VALUES( '10/11','001577', 3,'36387','10:00:00','10:30:00', 0.5); INSERT INTO @Timetable_tb VALUES( '10/11','001577', 3,'36389','09:00:00','10:30:00', 1.5); INSERT INTO @Timetable_tb VALUES( '10/11','001577', 2,'36392','13:00:00','14:30:00', 1.5); And I want to select the events which have an overlap SELECT DISTINCT a.AcadPeriod, a.CC_staffID, a.week_day, a.EventID, a.start_time, a.end_time FROM @Timetable_tb a WHERE EXISTS (SELECT DISTINCT bb.AcadPeriod, bb.CC_staffID, bb.week_day, bb.start_time, bb.end_time FROM @Timetable_tb bb WHERE bb.CC_StaffID = a.CC_StaffID AND bb.week_day = a.week_day AND bb.EventID <> a.EVENTID AND (bb.start_time < a.end_time AND bb.start_time < a.end_time)) ORDER BY a.CC_staffID, a.week_day, a.start_time I have all the events pulled out which overlap but I am unable to calculate the overlap time that this person is timetabled discounting the period where there is an overlap. Also there maybe multiple overlaps that I need to disregard. e.g. on day 2, there is three timeslots that overlap 9:00 – 10:00 ,9:30 to 10:30 and 9:00 to 09:15. I only want to calulate the duration 09:00 to 10:30 for day 2 as 1 hr 30mins and not 1 hr + 1hr + 15 mins. Can anyone help . Many thanks Conor. Brico