CODEWITHSUNDEEP
sqlsql-serversql-update
TWaller
TWaller

Reputation: 81

How to revert field back to prior value when another field causes the change in TSQL

I have a table that has a JobStartDt column and a TermDt column. When the termination date is populated, the JobStartDt is overwritten with the termination date (it's like that in the source data that I receive from HR). I need my SQL script to find the JobStartDt from a previous ReportDate and update the value that changed when the TermDt is populated (there is a record for each individual everyday). So in the below example, this person has a TermDt of 7/17 - I need the JobStartDt to revert back to 9/16/15. Is that possible in SQL? I am using SQL Server Management Studio 2014. Thanks in advance!

table records

Upvotes: 0

Views: 66

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Using CTE and ROW_NUMBER() funcion, for each employ, find last record before termination and update rows after termination with correct JobStartDt value

WITH CTE_BeforeTerm AS 
(
  SELECT * FROM YourTable
  WHERE TermDT IS NULL
)
, CTE_RN
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY ReportDate DESC) RN 
  FROM CTE_BeforeTerm
)
UPDATE t
SET t.JobStartDt = r.JobStartDt
FROM YourTable t 
INNER JOIN CTE_RN r ON r.EmpID = t.EmpID AND r.RN = 1
WHERE t.TermDt IS NOT NULL

Upvotes: 1

Related Questions