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