1 2 3 4 5 6 7 8 9 10 11
|
SELECT p1.employee_number, p1.surname AS employee_surname, p1.forenames AS employee_forenames, p1.continuous_start_date AS employee_start_date,
p1.termination_date AS employee_termination_date, p2.employee_number AS manager_number, p2.surname AS manager_surname,
p2.forenames AS manager_forenames, p2.email_address AS manager_email, p2.generic_department AS manager_generic_department_code,
g2.description AS manager_generic_department_description, co2.description AS manager_country_description
FROM xcountry co2 RIGHT OUTER JOIN
people p1 LEFT OUTER JOIN
[color=red] manager m1 ON p1.unique_identifier = m1.parent_identifier LEFT OUTER JOIN [/color]
people p2 ON m1.manager_number = p2.employee_number LEFT OUTER JOIN
xgendept g2 ON p2.generic_department = g2.code ON co2.code = p2.country
WHERE (p1.termination_date IS NOT NULL) AND (DATEDIFF(day, p1.termination_date, GETDATE() - 1) < 0) AND (DATEDIFF(day, GETDATE() - 1, p1.termination_date) < 31) |