SQLTeam.com | Weblogs | Forums

Help with UPDATE query from a SELECT using subqueries within one table

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
END

FROM
(
SELECT E.EmployeeNbr, E.Jobtitle, E.Week
FROM Employees E
WHERE E.Week = 41
) Q1 --Current week whose jobtitles I want to update

  RIGHT 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!

Data

Drop table if exists #e
go
Create table #e (EmpNo int, WeekNo tinyint, JobTitle char(1))
go

insert into #e values
(11111,41,'B'),
(11111,40,'A'),
(11111,39,'X'),
(22222,41,'X'),
(22222,40,'Y'),
(22222,39,'Z')

Query

;with cte as (select EmpNo, JobTitle
from #e
where WeekNo = 40)

update E
	set jobTitle = c.JobTitle
  from #e e
	join cte c
		on c.empno = e.empno
		and c.jobtitle <> e.Jobtitle
		and e.WeekNo = 41