I have 2 tables (1. PLATE, 2. MOVEMENT). First table contains some duplicate plate numbers. Every plate number (Fields: PLATE_ID, PLATE_COUNTRY, PLATE_NUMBER) has an unique id, which exists in movement table (Fields: MOV_ID, MOV_ENTRYDATE, MOV_EXITDATE) too. How can I find out all duplicate entries with the movement date, where MOV_EXITDATE in first entry = MOV_ENTRYDATE in second movement data.
PLATE_ID PLATE_NUMBER PLATE_COUNTRY 1 AS25 UK 2 AS25 UK 3 GG10 D 4 GG10 D 5 BB40 I MOV_ID MOV_COUNTRY MOV_ENTRYDATE MOV_EXITDATE 1 GB 2019-03-01 2019-03-05 2 GB 2019-03-05 0001-01-01 3 D 2019-02-01 2019-02-20 4 D 2019-02-21 0001-01-01 5 I 2019-01-01 0001-01-01
PLATE_ID PLATE_NUMBER PLATE_COUNTRY MOV_ENTRYDATE MOV_EXITDATE 1 AS25 UK 2019-03-01 2019-03-05 2 AS25 UK 2019-03-05 0001-01-01
Note: Plate AS25 is duplicate in table PLATE and MOV_EXITDATE = MOV_ENTRYDATE.
I'm not successful with my query below.
Select PLATE_ID, PLATE_NUMBER, PLATE_COUNTRY, MOV_ENTRYDATE, MOV_EXITDATE from PLATE t inner join (Select PLATE_ID, PLATE_COUNTRY, count(PLATE_NUMBER) from PLATE group by PLATE_ID, PLATE_COUNTRY having (count(PLATE_NUMBER) > '1')) k on t.PLATE_ID = k.PLATE_ID and t.PLATE_COUNTRY = k.PLATE_COUNTRY inner join (Select MOV_ID, MOV_COUNTRY, MOV_ENTRYDATE, min(MOV_EXITDATE) as minexitdate from MOVEMENT group by MOV_ID, MOV_COUNTRY, MOV_ENTRYDATE) p on t.PLATE_ID = p.MOV_ID and t.PLATE_COUNTRY = p.MOV_COUNTRY inner join (Select MOV_ID, MOV_COUNTRY, MOV_EXITDATE, max(MOV_ENTRYDATE) as maxentrydate from MOVEMENT group by MOV_ID, MOV_COUNTRY, MOV_EXITDATE) p on t.PLATE_ID = p.MOV_ID and t.PLATE_COUNTRY = p.MOV_COUNTRY where minexitdate = maxentrydate
Thanks, for every helpful tip.