Latest Record Date Script between Two Tables

[griffin2571]

I have two different tables where right now if there is a car vin number that are identical then table two will be considered the latest record. However, I am trying to change the logic where the table with the latest date will be the chosen record to be displayed.

Car Vin Number	Date	Repair
160002571	3/14/2021	Oil Change

Table 1

Car Vin Number	Date	Repair
160002571	2/14/2023	Oil Change

Table 2

Current Script

Temp:
NoConcatenate
Load *
Resident CarRepairRecords

Concatenate (Temp)
Load *,
Resident Temp
where Dup_Record='Y' and max;
and Source='Table 1'

How would I script the query so that it chooses table two for this example based off the latest record between both tables?

[justISO]

Hi, maybe someone can suggest more elegant solution, but how I would do: load both tables into one and mark latest record with indicator. It depends on whether the latest date is taken only at VIN level or at VIN+Repair level (as same VIN can repair different things), so you need to adjust "group by" part, but solution could be something like this:

table1:
LOAD * INLINE [
vin, date, repair
A1, 2021-01-01, oil
B2, 2021-06-01, engine
C3, 2021-07-01, brakes];

CONCATENATE (table1)
table2:
LOAD * INLINE [
vin, date, repair
A1, 2023-02-01, oil
B2, 2022-09-01, engine
C3, 2021-09-01, engine];

JOIN (table1)
LOAD 
vin, 
max(date) as date,
repair,
'Y' as ind_latest //indicator of latest record
RESIDENT table1
GROUP BY vin, repair; //combination VIN+Repair

//UNCOMMENT BELLOW TO HAVE ONLY LATEST ROWS
// NoConcatenate 
// only_unique:
// LOAD 
// vin, date, repair
// RESIDENT table1
// WHERE ind_latest='Y';
// DROP TABLE table1;