Hi,
I am facing the following problem:
I would like to retrieve some rows with following sql:
SELECT DISTINCT d.DATE_DT,
d.TERMINAL_ID,
d.TIME_TM AS HOME_DEP,
a.TIME_TM AS HOME_ARR,
d.CAR_KM AS DEP_KM,
a.CAR_KM AS ARR_KM,
a.CAR_KM – d.CAR_KM AS TOTAL_KM,
a.TIME_TM – d.TIME_TM AS TOTAL_TIME
FROM COLLECT d
INNER JOIN COLLECT a
ON d.TERMINAL_ID = a.TERMINAL_ID
AND d.DATE_DT = a.DATE_DT
AND d.TIME_TM < a.TIME_TM
WHERE d.JOB = 50
AND a.JOB = 51
AND d.TERMINAL_ID = 166
ORDER BY d.DATE_DT,
d.TERMINAL_ID,
d.TIME_TM
This sql works fine when there is only one departure per day, but when there are 2 departures a day, this sql does not return 2, but 3 rows, where the 2nd row is the combination of departure time 1 and arrival time 2
Can anyone tell a sollution on how to manage this?
Thanks