General discussion

Locked

Unable to retrieve unique rows from table

By e.bollen@live;be ·
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

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

You miss a matching criterion.

by gustsmits In reply to Unable to retrieve unique ...

Your query matches every arrival with all preceeding departures. You need a criterion to match an arrival with his unique corresponding departure.
If I suppose that an arrival should match the departure just before it then add the following to your WHERE-clause:
AND a.TIME_TM <= (SELECT ISNULL(MIN(x.TIME_TM)),'2999-12-31 23:59:59') FROM COLLECT x WHERE x.TERMINAL_ID = d.TERMINAL_ID AND x.DATE_DT = d.DATE_DT AND x.JOB = d.JOB AND x.TIME_TM>d.TIME_TM)
This limits the arrival time to the interval between the departure time and the next departure time.
I'm not 100% sure about the syntax (no SQL Server here to test it) but the logic should work.

Back to Software Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums