July 29, 2016 at 9:41 pm
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!!
July 29, 2016 at 10:23 pm
Please provide CREATE TABLE, INSERT INTO statements with sample data, so we have something to work with. It is very tedious to convert sample data from your post into actual populated SQL table.
It seems that you want to see what happened on given date. However, it may turn out to be more complicated. If you want more than to see "What was each driver doing on given day", then tweaking table(s) design may be in order...
I'll be back tomorow morning, Eastern Time.
July 30, 2016 at 12:45 am
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
July 30, 2016 at 3:30 am
Thank you for your help!!
Each driver dispatch date and truck number represents a separate truck run and I need to find only those matches where the first driver is sleeping and the second driver is off duty for each separate truck run. The driver table has hundreds of separate truck runs each day.
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
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
9 2016-06-25 08:00:00.000 2016-06-25 21:45:00.000 25-JUN-16 00:00:00 1356 5 SLEEP 25-JUN-16 09:00:00 25-JUN-16 19:00:00
14 2016-06-25 12:01:00.000 2016-06-25 16:59:00.000 25-JUN-16 00:00:00 1356 5 OFDTY 25-JUN-16 12:01:00 25-JUN-16 17:00:00
July 30, 2016 at 10:34 am
first off please read this
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
then post back with the necessary script that allows us to easily set on our own systems.
suggest that you also post some more sample data....that includes truck runs where you arent going to get results (that way we can be sure our code works)
are there only ever two drivers per truck run?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 30, 2016 at 11:47 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply