I am trying to run a query that finds name mismatches between systems. Basically, I'm saying Select EMP_ID, System1_Name, System2_name, where System 1_name <>System2_name.
The problem that I am having is that the table for system1 includes multiple rows for one person (in cases of name changes) with an effective date. The table for system2 only includes the current name.
EMPLID Name EffDT
1 Smith 1/1/2012
1 Jones 1/1/2018
2 Miller 3/1/2016
3 Teams 1/4/2019
3 Frank 3/5/2020
3 Lee 1/1/2021
The results I would expect to see from the query would be:
EMPL_ID Table1_Name Table 2 Name
1 Jones Jones
2 Miller Miller
3 Lee Frank
New to data point AND SQL, so any help would be appreciated!