• I am glad you figured it out.  I am a little confused why what I wrote didn't work though.

    Did you change any of the code I posted?  It should have been just a simple copy-paste from what I wrote to your system.  No code changes should have been required as it is getting the data from the Employee_Change table.

    Also, just to confirm, you were using Microsoft SQL Server 2008 or higher, correct?
    It is odd that it would give you an error when you add the join but give you results without the join.  What results do you get when you run:
    WITH cte AS
    (
    SELECT [Staff_ID] ,
      [Transaction_date] ,
      [Staff_name] ,
      [old_store] ,
      [new_store],
      ROW_NUMBER() OVER (partition BY Staff_id ORDER BY Transaction_Date) AS [rn]
    FROM Employee_Change
    )
    SELECT *
    FROM cte

    and when you run:
    WITH cte AS
    (
    SELECT [Staff_ID] ,
      [Transaction_date] ,
      [Staff_name] ,
      [old_store] ,
      [new_store],
      ROW_NUMBER() OVER (partition BY Staff_id ORDER BY Transaction_Date) AS [rn]
    FROM Employee_Change
    )
    SELECT *
    FROM cte cte1
    FULL OUTER JOIN cte cte2 ON [cte1].[rn] = [cte2].[rn] + 1

    I am just trying to figure out why using CTE's didn't work for you.  CTE's are quite helpful in SQL.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.