Max Effective Dates

Toad Data Point
1

Can anyone tell me whether there's an easy way to incorporate max effective date in to query builder? I know this was a topic several years back, but hoping some improvements have been made to make this easier.

Issue: I'm trying to run a query that identifies records where the first name does NOT match between two systems. The problem is that the table includes all prior names, not just the most recent. I've built the query in query builder then tried to edit the SQL statement, but keep getting errors.

SELECT PS_CSM_MBR_DEMOGR.CSM_MBR_DEMOGR_ID,
PS_CSM_MBR_DEMOGR.CSM_FIRST_NM AS "ERM First Name",
PS_NAMES.FIRST_NAME AS "INPAS First Name",
PS_CSM_MBR_DEMOGR.CSM_STATUS_ID
FROM SYSADM.PS_PERSON PS_PERSON
CROSS JOIN SYSADM.PS_NAMES PS_NAMES
INNER JOIN PERF.PS_CSM_MBR_DEMOGR PS_CSM_MBR_DEMOGR
ON (PS_NAMES.EMPLID = PS_CSM_MBR_DEMOGR.CSM_MBR_DEMOGR_ID)
WHERE (UPPER (PS_CSM_MBR_DEMOGR.CSM_FIRST_NM) != UPPER (PS_NAMES.FIRST_NAME))

2

Typically, I would use a CTE to accomplish this. I'm a SQL Server dev, so here is an example in TSQL:

;WITH cte AS(SELECT date, fname, memberID, row_number() OVER(ORDER BY date DESC) as Ordinal
FROM table1)

SELECT t2.fname, t2.memberID
FROM table2 as t2
WHERE NOT EXISTS(SELECT * FROM cte 
  WHERE cte.memberID=t2.memberID and cte.Ordinal=1)