SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Create tables

CREATE TABLE start_dates (
    id_number INT,
    date_of_birth DATE,
    employment_start_date DATE,
    employed_or_departed VARCHAR(20)
);

CREATE TABLE new_database (
    id_number INT,
    full_name VARCHAR(100),
    date_of_birth DATE,
    occupation VARCHAR(100)
);

CREATE TABLE old_database (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    id_number INT,
    date_of_birth DATE,
    occupation VARCHAR(100)
);
-- Insert sample data

INSERT INTO start_dates (id_number, date_of_birth, employment_start_date, employed_or_departed)
VALUES
(1, '1990-05-15', '2015-01-01', 'Employed'),
(2, '1985-09-20', '2018-03-10', 'Employed'),
(3, '1992-11-30', '2019-07-20', 'Employed'),
(4, '1988-04-10', '2017-11-05', 'Departed'),
(5, '1995-02-28', '2020-02-15', 'Employed');

INSERT INTO new_database (id_number, full_name, date_of_birth, occupation)
VALUES
(1, 'John Doe', '1990-05-15', 'Pilot'),
(2, 'Jane Smith', '1985-09-20', 'Engineer'),
(3, 'Michael Johnson', '1992-11-30', 'Navigator'),
(4, 'Emily Brown', '1988-04-10', 'Cabin Crew'),
(5, 'David Wilson', '1995-02-28', 'Pilot');

INSERT INTO old_database (first_name, last_name, id_number, date_of_birth, occupation)
VALUES
('John', 'Doe', 1, '1990-05-15', 'Captain'),
('Jane', 'Smith', 2, '1985-09-20', 'Technician'),
('Michael', 'Johnson', 3, '1992-11-30', 'Navigator'),
('Emily', 'Brown', 4, '1988-04-10', 'Flight Attendant'),
('David', 'Wilson', 5, '1995-02-28', 'Co-Pilot');
15 rows affected

SELECT
    nd.full_name AS Full_Name,
    sd.employment_start_date,
    nd.occupation AS latest_occupation,
    od.occupation AS previous_occupation
FROM
    start_dates sd
INNER JOIN
    new_database nd ON sd.id_number = nd.id_number
INNER JOIN
    old_database od ON nd.id_number = od.id_number
WHERE
    sd.employed_or_departed = 'Employed'
    AND nd.occupation <> od.occupation;


Full_Name
employment_start_date
latest_occupation
previous_occupation


John Doe
2015-01-01
Pilot
Captain

Jane Smith
2018-03-10
Engineer
Technician

David Wilson
2020-02-15
Pilot
Co-Pilot

Full_Name	employment_start_date	latest_occupation	previous_occupation
John Doe	2015-01-01	Pilot	Captain
Jane Smith	2018-03-10	Engineer	Technician
David Wilson	2020-02-15	Pilot	Co-Pilot