Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2017
    Posts
    1

    Unhappy Unanswered: Change the old store to new store with the same table

    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
    1 AA 1.5.2017 4 1

    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
    Attached Thumbnails Attached Thumbnails Capture.JPG   Capture1.JPG  
    Last edited by Sam Myat; 11-14-17 at 13:17.

  2. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    I think you want MAX(transaction_Date) that is less than. Try a sub-select to get that, instead of explicit inner join

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •