losstww (7/29/2016)
I have a driver table that contains daily driving records for each day. There are two drivers per tractor, and I want to return all records where one driver is sleeping and the other driver is off duty at the same time. The driver table looks like:DRIVER_DISPATCH_DT TRUCK DRIVER_ID STATUS DRIVER_START_DATE DRIVER_END_DATE
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
Results should be:
DATE | TRUCK | DRIVER_ID | STATUS | START_DT | END_DT
---------------- -------- ------------- ----------- ----------------------- -------------------------------
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 OFDTY 25-JUN-16 12:01:00 25-JUN-16 17:00:00
I tired the following sql statement but it does not work correctly:
SELECT A.*
FROM DRIVER A
INNER JOIN DRIVER B
ON B.START_DT > A.START_DT AND B.START_DT < A.END_DT AND A.ID <> B.ID;
Any help would be appreciated!!
I think this works for small sample set....but as previously posted, please provide more data that covers all possibilites
WITH cte as (
SELECT driver.*,
LAG(DRIVER_ID, 1, DRIVER_ID) OVER(PARTITION BY TRUCK ORDER BY driver_start_date) driver_lag,
LEAD(DRIVER_ID, 1, DRIVER_ID) OVER(PARTITION BY TRUCK ORDER BY driver_start_date) driver_lead,
LEAD(driver_start_date, 1, driver_start_date) OVER(PARTITION BY TRUCK ORDER BY driver_start_date) sdate_lead,
LAG(driver_end_date, 1, driver_end_date) OVER(PARTITION BY TRUCK ORDER BY driver_start_date) edate_lag
FROM <yourtablename>
WHERE STATUS IN('SLEEP', 'OFDTY')
)
SELECT DRIVER_DISPATCH_DT,
TRUCK,
DRIVER_ID,
STATUS,
DRIVER_START_DATE,
DRIVER_END_DATE
FROM CTE
WHERE
((DRIVER_ID <> driver_lead AND DRIVER_END_DATE > sdate_lead)
OR
(DRIVER_ID <> driver_lag AND DRIVER_START_DATE < edate_lag))
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day