hello all,

please explain below query used in solution below, thanks in advance!

ELECT 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 

------------------------

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