Here is a suggestion towards a solution or rather a sketch of one which simply outlines one method of finding multiple overlaps. Since it works on the interval of minutes, the results are slightly different from the posted expected output.
😎
USE TEEST;
GO
SET NOCOUNT ON;
;WITH T(N) AS (SELECT N FROM ( VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
,SAMPLE_DATA(SD_RID,DRIVER_DISPATCH_DT,TRUCK,DRIVER_ID,STATUS,DRIVER_START_DATE,DRIVER_END_DATE) AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY DRIVER_START_DATE,DRIVER_ID) AS SD_RID, DRIVER_DISPATCH_DT,TRUCK,DRIVER_ID,STATUS,DRIVER_START_DATE,DRIVER_END_DATE
FROM (VALUES
('25-JUN-16 00:00:00',1234,1,'SLEEP','25-JUN-16 08:30:00','25-JUN-16 23:12:26')
,('25-JUN-16 00:00:00',1234,2,'DRIVE','25-JUN-16 08:32:14','25-JUN-16 12:00:00')
,('25-JUN-16 00:00:00',1234,2,'OFDTY','25-JUN-16 12:01:00','25-JUN-16 17:00:00')
,('25-JUN-16 00:00:00',1234,2,'DRIVE','25-JUN-16 17:01:00','25-JUN-16 19:30:00')
,('25-JUN-16 00:00:00',2300,1,'DRIVE','25-JUN-16 10:00:00','25-JUN-16 18:29:00')
,('25-JUN-16 00:00:00',2300,2,'SLEEP','25-JUN-16 10:01:00','25-JUN-16 18:28:00')
) AS X(DRIVER_DISPATCH_DT,TRUCK,DRIVER_ID,STATUS,DRIVER_START_DATE,DRIVER_END_DATE)
)
-- LIMIT THE WORKSET TO THE RELEVANT ENTRIES
,INTERESTING_ENTRIES AS
(
SELECT
SD.SD_RID
,SD.DRIVER_DISPATCH_DT
,SD.TRUCK
,SD.DRIVER_ID
,SD.STATUS
,SD.DRIVER_START_DATE
,SD.DRIVER_END_DATE
FROM SAMPLE_DATA SD
WHERE SD.STATUS IN ('SLEEP','OFDTY')
)
-- A CALENDAR SEED FOR THE POSSIBLE OVERLAPS
,MIN_DATE_RANGE AS
(
SELECT
MIN(ID.DRIVER_START_DATE) AS MIN_DATE
,DATEDIFF(MINUTE,MIN(ID.DRIVER_START_DATE),MAX(ID.DRIVER_END_DATE)) AS DD_MINUTES
FROM INTERESTING_ENTRIES ID
)
-- INLINE TALLY TABLE TO GENERATE THE CALENDAR
,NUMS(N) AS
(
SELECT 0 UNION ALL
SELECT TOP((SELECT DD_MINUTES FROM MIN_DATE_RANGE)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6
)
-- MARKING THE ACTIVE CALENDAR ENTRIES
,ACTIVE_RANGES AS
(
SELECT
IE.SD_RID
,DATEADD(MINUTE,NM.N, MIMX.MIN_DATE) AS DT_MINUTE
FROM NUMS NM
CROSS APPLY MIN_DATE_RANGE MIMX
CROSS APPLY INTERESTING_ENTRIES IE
WHERE DATEADD(MINUTE,NM.N, MIMX.MIN_DATE) >= IE.DRIVER_START_DATE
AND DATEADD(MINUTE,NM.N, MIMX.MIN_DATE) < IE.DRIVER_END_DATE
)
-- COUNTING INSTANCES FOR EACH CALENDAR ENTRY
,FILERING_OVERLAPS AS
(
SELECT
AR.SD_RID
,AR.DT_MINUTE
,COUNT(*) OVER
(
PARTITION BY AR.DT_MINUTE
) AS SESS_COUNT
FROM ACTIVE_RANGES AR
)
-- MARK START AND END OF EACH ENTRY
,EFFECTIVE_OVERLAPS AS
(
SELECT
FO.SD_RID
,MIN(FO.DT_MINUTE) AS OVL_START
,MAX(FO.DT_MINUTE) AS OVL_END
FROM FILERING_OVERLAPS FO
WHERE FO.SESS_COUNT > 1
GROUP BY FO.SD_RID
)
-- DISPLAY RELEVANT RESULTS
SELECT
SD.SD_RID
,EO.OVL_START
,EO.OVL_END
,SD.DRIVER_DISPATCH_DT
,SD.TRUCK
,SD.DRIVER_ID
,SD.STATUS
,SD.DRIVER_START_DATE
,SD.DRIVER_END_DATE
FROM EFFECTIVE_OVERLAPS EO
INNER JOIN SAMPLE_DATA SD
ON EO.SD_RID = SD.SD_RID;
Results from the sample data provided
SD_RID OVL_START OVL_END DRIVER_DISPATCH_DT TRUCK DRIVER_ID STATUS DRIVER_START_DATE DRIVER_END_DATE
--------- ----------------------- ----------------------- ------------------ ----------- ----------- ------ ------------------ ------------------
1 2016-06-25 10:01:00.000 2016-06-25 18:27:00.000 25-JUN-16 00:00:00 1234 1 SLEEP 25-JUN-16 08:30:00 25-JUN-16 23:12:26
4 2016-06-25 10:01:00.000 2016-06-25 18:27:00.000 25-JUN-16 00:00:00 2300 2 SLEEP 25-JUN-16 10:01:00 25-JUN-16 18:28:00
5 2016-06-25 12:01:00.000 2016-06-25 16:59:00.000 25-JUN-16 00:00:00 1234 2 OFDTY 25-JUN-16 12:01:00 25-JUN-16 17:00:00