CODEWITHSUNDEEP
mysqldatetimeleft-join
Slimpunkerz
Slimpunkerz

Reputation: 15

Find the nearest date stored in another table according to several "master date", data in common but no ID in common

Sorry for the title, this was quite hard to explain this in few words (that shows that I don't understand the problem completely).

Service Table: (there are other field but not relevant here)
-------------------------------
| PK1    | REF     | Ops_Date |
-------------------------------
| 1      | ABCD    | 20180211 |
| 2      | EFGH    | 20180315 |
| 3      | ABCD    | 20180412 |
-------------------------------

Dim Table: 
-------------------------------
| PK2    | REF     | Arrival  |
-------------------------------
| 57     | ABCD    | 20180108 |
| 58     | ABCD    | 20180201 |
| 59     | EFGH    | 20180309 |
| 60     | EFGH    | 20180311 |
| 61     | ABCD    | 20180409 |
| 62     | ABCD    | 20180411 |
-------------------------------

Result:
--------------------------------------------------------
| PK1    | REF     | Ops_Date | PK2      | Arrival     |       
--------------------------------------------------------
| 1      | ABCD    | 20180211 | 58       | 20180201    |
| 2      | EFGH    | 20180315 | 60       | 20180311    | 
| 3      | ABCD    | 20180412 | 62       | 20180411    |
--------------------------------------------------------

What I need is a left join of the service table with the dim table. The criterias ON are the REF which is common to both and then the nearest arrival from the ops_date. The ops date will always be after the arrival, that's what I need always the nearest before the ops_date.

What I try :

   SELECT 
      PK1, 
      REF,
      ops_date, 
      PK2, 
      arrival

   FROM 
     service 

   LEFT JOIN dim
   ON service.REF = dim.REF

I suppose that a condition should be added here to manage to find the nearest date for each record during the Left join

I'm not sure this kind of think are even possible without any ETL or procedures but if anybody have a clue It would be much appreciated.

Thanks in advance for your help.

Upvotes: 0

Views: 24

Answers (1)

Nick
Nick

Reputation: 147146

You just need to add a condition to your JOIN that says that the Arrival time in Dim is the maximum Arrival time that is less than the associated Ops_Date value for that Service. You can find that result with a correlated subquery:

SELECT s.*, d.PK2, d.Arrival
FROM Service s
LEFT JOIN Dim d ON d.REF = s.REF 
               AND d.Arrival = (SELECT MAX(Arrival)
                                FROM Dim d2
                                WHERE d2.REF = s.REF
                                  AND d2.Arrival < s.Ops_Date)

Output:

PK1 REF     Ops_Date    PK2 Arrival
1   ABCD    20180211    58  20180201
2   EFGH    20180315    60  20180311
3   ABCD    20180412    62  20180411

Demo on dbfiddle

Upvotes: 1

Related Questions