DRIVERNAME,
OUT_DATE,
OUT_TIME
from (
SELECT BADGENO,
DRIVERNAME,
OUT_DATE,
OUT_TIME,
NVL((TO_DATE(OUT_TIME,'HH24:MI') - TO_DATE(LAG(OUT_TIME) OVER
(PARTITION BY BADGENO ORDER BY
BADGENO,OUT_DATE,OUT_TIME),'HH24:MI'))*24*60,20) PREV_VALUE,
(TO_DATE(OUT_TIME,'HH24:MI') - TO_DATE(LEAD(OUT_TIME) OVER
(PARTITION BY BADGENO ORDER BY BADGENO,OUT_DATE,OUT_TIME),'HH24:MI'))*24*60
NEXT_VALUE
FROM (
SELECT BADGENO,
DRIVERNAME AS DRIVERNAME,
TO_DATE(TO_CHAR(OUT_DATETIME,'DD/MM/RRRR'),'DD/MM/RRRR') AS
OUT_DATE,
TO_CHAR(OUT_DATETIME,'HH24:MI') AS OUT_TIME
FROM EMPTY_CONTR
WHERE TRUCK_CO = 'GMS'
AND TRANSTYPE = 'BAK'
AND TO_DATE(OUT_DATETIME,'DD/MM/RRRR') >=
TO_DATE(:FROM_DATE,'DD/MM/RRRR')
AND TO_DATE(OUT_DATETIME,'DD/MM/RRRR') <
TO_DATE(:END_DATE,'DD/MM/RRRR')+1
ORDER BY BADGENO,OUT_DATE,OUT_TIME)
)
WHERE PREV_VALUE >= 15
