Considering time by excluding overlapping and gaps

by Amol 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

Tags:

SQL Server Scripts

Category

Recent Posts

Tag cloud