I am using the below query to get the results I have shown in the below table. I am looking to modify the query to get the expected result as shown in last table. I would like to align the latest timestamp (start_time) with the manufacture_date (string). Also, if a "manufacture_date" from Vehicles table doesn't have any result in modem_auth table then the lifecycle_mode, auth_status, start_time, end_time column should show NULL in the result. Basically I would like to show all the records from vehicles table.

    SELECT v.vin,
           date_Format(v.manufacture_date, 'yyyy-MM-dd') AS event_date,
           "Vehicle Manufactured" AS event_desc,
           m.lifecycle_mode,
           m.auth_status,
           m.start_time,
           m.end_time
      FROM vehicles v
INNER JOIN (SELECT vin, MAX(created_on) AS max_created_on FROM vehicles GROUP BY vin) v2 
        ON v.vin = v2.vin AND 
           v.created_on = v2.max_created_on
 LEFT JOIN modem_auth m 
        ON v.vin = m.vin AND
           (date_Format(v.manufacture_date, 'yyyy-MM-dd') BETWEEN TO_DATE(m.start_time) AND TO_DATE(m.end_time)) AND
           save.vin_2 IN ('LMN12345', 'XYZ12345', 'ABC12345')
     WHERE m.partition_region = 'NA' AND
           m.partition_country = 'USA'

Result I am getting -

Expected Result -

CodePudding user response：

The issue is that you have multiple rows coming out of table m (modem_auth) for a single vin. You need to boil the rows coming out of m down to a maximum of one per vin, which means you will need to make a table expression out of it that includes a group by vin clause. (I'm a little confused about the event_date column in the first table, as if vin is the primary key of vehicles you wouldn't have two different dates for the same vin as the table shows for ABC12345.)

What you need to do is create a table expression based on modem_auth that identifies the one row per vin that you want to use in the outer join to vehicles, that row being the one with the latest start_time. So replace

LEFT JOIN modem_auth m 
 ON v.vin = m.vin AND
       (date_Format(v.manufacture_date, 'yyyy-MM-dd') BETWEEN TO_DATE(m.start_time) AND TO_DATE(m.end_time))

with

LEFT JOIN modem_auth m
   INNER JOIN (select ma2.vin, max(ma2.start_time) AS start_time from modem_auth ma2
               INNER JOIN vehicles v2 ON v2.vin = ma2.vin
                   AND date_Format(v2.manufacture_date, 'yyyy-MM-dd') BETWEEN
                        TO_DATE(ma2.start_time) AND TO_DATE(ma2.end_time)
               group by ma2.vin) mamax ON m.vin = mamax.vin AND m.start_time = mamax.start_time
 ON v.vin = m.vin AND
       (date_Format(v.manufacture_date, 'yyyy-MM-dd') BETWEEN TO_DATE(m.start_time) AND TO_DATE(m.end_time))