I want to update the jobtitles in my Employee table for a certain week (e.g. 41) based on the previous week's (e.g. 40) jobtitles.
Please find current table, table after current query and desired results attached:
This is my current query which updates all employees' weeks' Jobtitles to the job title in week 40. I
I only want to update the jobtitles in week 41 based on week 40 where EmpNbr matches and Jobtitles are different between week 41 and week 40.
UPDATE Employees
SET Jobtitle =
CASE
WHEN Q2.Jobtitle = 'A' AND Q1.Jobtitle = 'B' THEN 'A'
WHEN Q2.Jobtitle = 'C' AND Q1.Jobtitle = 'D' THEN 'C'
ELSE Q1.Jobtitle
ENDFROM
(
SELECT E.EmployeeNbr, E.Jobtitle, E.Week
FROM Employees E
WHERE E.Week = 41
) Q1 --Current week whose jobtitles I want to updateRIGHT JOIN ( SELECT E.EmployeeNbr, E.Jobtitle, E.Week FROM Employees E WHERE E.Week = 40 ) Q2 -- previous week which I use to update jobtitles in current week ON Q1.EmployeeNbr = Q2.EmployeeNbr
WHERE Q1.Jobtitle <> Q2.Jobtitle
AND Q1.WeekNum= 41
Any help much appreciated!