# Considering time by excluding overlapping and gaps

26. February 2011 19:06

If we need to deal with schedules, we often require calculating total period by excluding overlap and schedule gaps.  Here is the scenario. Below table contains one schedule. According the schedule total hours should be 6 hours by excluding gaps and overlaps.

 9:00 AM 10:00 AM 11:00 AM 12:00 PM 1:00 PM 2:00 PM 3:00 PM 4:00 PM 5:00 PM 1hr 1hr 1hr 1hr 1hr 1hr 1hr 1hr

Below script is used to populate some sample data according the above schedule and script to get the actual hours (you can say working hours).

To get the total working hours, first need to calculate total hours between very first time and last time.  Then subtract the gaps between schedules if there are any.

IF OBJECT_ID('XYZ') IS NOT NULL

DROP TABLE XYZ

GO

CREATE TABLE XYZ

(

id int identity(1,1),

ST smalldatetime NOT NULL,

ET smalldatetime NOT NULL

)

GO

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 9:00AM', '2010-01-01 10:00AM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 9:00AM', '2010-01-01 12:00PM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 1:00PM', '2010-01-01 2:00PM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 3:00PM', '2010-01-01 5:00PM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 11:00AM', '2010-01-01 12:00PM')

GO

WITH Gaps(Gap) AS

(

SELECT COALESCE(SUM(DATEDIFF(MINUTE,ST,ET)), 0)

FROM (

SELECT MAX(P1.ET) AS ST, P2.ST AS ET

FROM XYZ AS P1

INNER JOIN XYZ AS P2 ON (P1.ST < P2.ST)

GROUP BY P2.ST

HAVING MAX(P1.ET) < P2.ST

) gaps

)

SELECT (

COALESCE(DATEDIFF(MINUTE, MIN(ST), MAX(ET)), 0)

- (SELECT Gap FROM Gaps)

) / 60.0 TotalHrs

FROM XYZ

SQL Server Scripts