My current code is:
SELECT
T1.SC,
T2.ACC,
T3.C_IDENT,
T4.TITLE,
T4.MID,
T4.SUR
FROM D_REG.AR_JOINT T1
LEFT JOIN E_BASE.ACC_TRANS T2
ON T1.SC = T2.SC
AND T1.ACC = T2.ACC
AND T2.IDENTIFEIR_END_DATE ='3000-12-31'
LEFT JOIN E_BASE.B_HOLDING T3
ON T2.M_IDENT = T3.C_IDENT
AND T3.EFF_END_DATE ='3000-12-31'
AND T3.P_HOLDING_END+DATE ='3000-12-31'
LEFT JOIN E_BASE.PNAM T4
ON T3.C_IDENT = T4.C_IDR
AND T4.EFF_END_DATE ='3000-12-31'
At the moment the code states to bring back a result if the End Dates = ‘3000-12-31’. However, if any amendments are made to any of the columns within the table it will create a duplicate row for that record. The original will have an end date of the date the amendment was made while the new record will have an end date of ‘3000-12-31’.
My code will then only bring back active records. If the record is no longer active it will have an end date such as ‘2018-10-15’ and so returns ? ? ? ? values from T4.
How can I amend the code to bring back the record that has the latest end date instead?
For Teradata, you can use Qualify to constrain on ODAP functions, ROW_NUMBER() in this case.. Can't fully answer your question without knowing more about your data. But basically, add to your query
QUALIFY ROW_NUMBER() OVER (PARTITION BY <YOUR KEY COLUMN(S) ORDER BY T4.EFF_END_DT DESC) = 1
That will give you the most recent row (based on your column eff_end_dt) for each "key".