0

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.

Example:

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

Result:

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.

  • Search this site for "gaps and islands". – mustaccio Mar 20 at 21:53
  • SELECT plate_number, plate_country, MIN(plate_id) min_plate_id FROM plate GROUP BY plate_number, plate_country. Join plate to it. Then join movement by plate_id, but group by min_plate_id. – Akina Mar 21 at 4:52
0

Good day.

Using the WITH syntax your subqueries may be more readable.

WITH PLATE_MOVEMENT AS (
    SELECT P.PLATE_ID, P.PLATE_NUMBER, P.PLATE_COUNTRY, 
           M.MOV_ENTRYDATE, M.MOV_EXITDATE
    FROM PRUEBA.PLATE P INNER JOIN PRUEBA.MOVEMENT M
         ON (P.PLATE_ID = M.PLATE_ID)
) 
SELECT A.PLATE_ID, A.PLATE_NUMBER, A.PLATE_COUNTRY, 
       A.MOV_ENTRYDATE, A.MOV_EXITDATE
FROM PLATE_MOVEMENT A INNER JOIN PLATE_MOVEMENT B
     ON  A.PLATE_NUMBER = B.PLATE_NUMBER
     AND A.PLATE_COUNTRY = B.PLATE_COUNTRY
     AND (A.MOV_EXITDATE = B.MOV_ENTRYDATE OR B.MOV_EXITDATE = A.MOV_ENTRYDATE)
-- optionally an order by
ORDER BY A.PLATE_COUNTRY, A.PLATE_NUMBER, A.MOV_ENTRY_DATE

With result

PLATE_ID    PLATE_NUMBER PLATE_COUNTRY MOV_ENTRYDATE MOV_EXITDATE
----------- ------------ ------------- ------------- ------------
          1 AS25         UK            03/01/2019    03/05/2019  
          2 AS25         UK            03/05/2019    01/01/0001  

  2 record(s) selected.

Quite obscure will be the condition

A.MOV_EXITDATE = B.MOV_ENTRYDATE OR B.MOV_EXITDATE = A.MOV_ENTRYDATE  

If omitted the second part, the last row in the chained collection of historic rows will be omitted. If omitted the first part, the first row in the chained collection of historic rows will be missing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.