CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
sk7730
sk7730

Reputation: 736

Old & New Values in SQL

I have a table like below.

 EmpName   Sta_CD   Comments
 AAAAAA     1        One
 AAAAAA     2        two
 AAAAAA     2        two_Updated
 AAAAAA     2        two_UpdatedAgain
 AAAAAA     3        Three
 AAAAAA     4        Four

I am trying to track Old and New Values of Sta_CD for Employee, Like Below one.

 EmpName Old_Sta_CD   Sta_CD   Comments
 AAAAAA      Null        1        One
 AAAAAA       1          2        two
 AAAAAA       2          2        two_Updated
 AAAAAA       2          2        two_UpdatedAgain
 AAAAAA       2          3        Three
 AAAAAA       3          4        Four

Can AnyOne Suggest me how to get this output?

Upvotes: 0

Views: 86

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93744

Try this if you are using SQL SERVER 2012+

Use LAG Function to get the previous row

SELECT EmpName,
       Lag(sta_cd)
         OVER(
           partition BY empname
           ORDER BY Sta_CD)old_Sta_CD,
       Sta_CD,
       Comments
FROM   <tablename>

SQL FIDDLE DEMO

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

You can use the ROW_NUMBER() function to do this, something like:

WITH Ranked
AS
(
  SELECT *,
    ROW_NUMBER() OVER(ORDER BY Sta_CD) AS RN
  FROM table1
)
SELECT 
  old.EmpName,
  new.Sta_CD AS Old_Sta_CD,
  old.Sta_CD,
  old.Comments
FROM Ranked AS old
LEFT JOIN Ranked AS new ON old.rn - 1 = new.rn;

This will give you:

| EMPNAME | OLD_STA_CD | STA_CD |         COMMENTS |
|---------|------------|--------|------------------|
|  AAAAAA |     (null) |      1 |              One |
|  AAAAAA |          1 |      2 |              two |
|  AAAAAA |          2 |      2 |      two_Updated |
|  AAAAAA |          2 |      2 | two_UpdatedAgain |
|  AAAAAA |          2 |      3 |            Three |
|  AAAAAA |          3 |      4 |             Four |

Upvotes: 2

Related Questions