how to view data as history of field value change

Question

Example

i have data like

id   employeeId Salary       Year
1      1           12000     2001
1      2           10000     2001
1      1           16000     2002
1      3           12000     2004
1      1           18000     2005
1      3           14000     2006

So employee ID 1 has 3 records in this and 2  has one and 3 has two record
i expect output as

EmployeeID  NewSalary Oldalary
1             18000    16000
1             16000    12000
2             10000    -
3             14000    12000`

Answer 1

This should work on SQL Server 2012 +

   ;with cte as
    (select employeeId,
            Salary AS 'NewSalary',
            lead(salary,1,0)over(partition by employeeid order by salary desc) as 'OldSalary',
            row_number()over(partition by employeeid order by salary desc) as rn        
     from Table_2 
     )

     select employeeId,
            NewSalary,
            OldSalary
     from cte
     where OldSalary <> 0 or (OldSalary = 0 and rn=1)

Answer 2

Completely revised answer - see the fiddle here for my old one. With the data as given by the OP, both my answer and @osiris_v6's answer work.

However, when I added extra data, both of our answers failed in the same way (see mine and @osiris_v6's). It's not a good idea to have your SQL's results depend on the number of records in this way.

What I did to solve the issue was the following

Create and populate a table

CREATE TABLE emp
(
  id INTEGER NOT NULL,
  employee_id INTEGER NOT NULL,
  salary INTEGER NOT NULL,
  s_year INTEGER NOT NULL
);

Populate it

INSERT INTO emp 
VALUES
(1   ,   1       ,    18000  ,   2005),
(1   ,   1       ,    16000  ,   2002),
(1   ,   1       ,    12000  ,   2001),
(1   ,   1       ,    10000  ,   2000), -- extra record
(1   ,   1       ,     8000  ,   1999), -- extra record

(1   ,   2       ,    10000  ,   2001),

(1   ,   3       ,    14000  ,   2006),
(1   ,   3       ,    12000  ,   2004);

And then run this query

WITH cte AS
(
  SELECT 
    employee_id,
    salary AS new_sal,
    LEAD(salary, 1, 0) OVER (PARTITION BY employee_id 
                                 ORDER BY salary DESC) AS old_sal,
    ROW_NUMBER()       OVER (PARTITION BY employee_id 
                                 ORDER BY salary DESC) AS rn        
    FROM emp 
)
SELECT 
  employee_id,
  new_sal,
  old_sal,
  rn
FROM cte
WHERE rn = 1 OR (rn = 2 AND old_sal != 0);  -- big change in this line!

Result

employee_id     new_sal     old_sal     rn
          1       18000       16000     1
          1       16000       12000     2
          2       10000           0     1
          3       14000       12000     1

Et voilà - the desired result (see the fiddle here), but one which will work with any dataset. Obviously, this new answer of mine owes a lot to @osiris_v6 and I thank them for the lessons I learned from it and, in particular, its elegance!

p.s. You might consider using a fiddle for your tables and data for any questions in the future? Also, please also always supply your version of SQL Server, it can make a real difference to the answer (my fiddle was SQL Server 2014, but it should work for 2012 also).

There are some articles on how to ask questions here on my profile - you might want to take a look? Window functions are very powerful and will repay many times the effort you put into learing them!

Welcome to the forum!