Change old store to new store

[Member 13338075]

I want to change the old store to new store by using the query in MSSQL. For example,

Staff_Id Name Transaction_Date New_Store Old_Store

1 AA 1.1.2017 1

1 AA 1.2.2017 2

1 AA 1.3.2017 3

1 AA 1.4.2017 1

1 AA 1.5.2017 4

I want to change the table to following like that
Staff_Id Name Transaction_Date New_Store Old_Store

1 AA 1.1.2017 1 NULL

1 AA 1.2.2017 2 1

1 AA 1.3.2017 3 2

1 AA 1.4.2017 1 3

I wrote the query like that, but it's not true, it's output randomly

Update EC1 SET EC1.Old_Store=EC.New_Store
From Employee_Change EC , Employee_Change EC1
WHERE EC.Staff_Id=EC1.Staff_Id
AND EC.Transaction_Date>=EC1.Transaction_Date

[Richard Deeming]

Something like this should work:

UPDATE
EC
SET
Old_Store =
(
SELECT TOP 1 New_Store
FROM Employee_Change As EC1
WHERE EC1.Staff_Id = EC.Staff_Id
And EC1.Transaction_Date < EC.Transaction_Date
ORDER BY EC1.Transaction_Date DESC
)
FROM
Employee_Change As EC
;

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[CHill60]

This has been answered at Change old store to new store[^] In future, do not cross-post across forums. Pick one and be patient - we do this in our spare time for free - sometimes it can take a little while :)