0
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`

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)
| improve this answer | |
  • +1 for an elegant solution (liked the use of offset and default of the LEAD() function). Both of our solutions work with OP's original data but fail in other scenarios - see my revised answer. I would appreciate any comments you might have. – Vérace Apr 10 at 0:25
0

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!

| improve this answer | |
  • Hope our combined efforts will answer the OP. As you well mentioned, it would have helped if more information was given. Your revised answer does produce the result set, but I'm not sure this is how it should behave. My understanding was to view a history of changes and i assumed is referring to the entire changes for an employee's salary, which my answer provides.Maybe @user2787096 will clarify. – osiris_v6 Apr 10 at 9:38
  • My understanding was that for this query, if there were only 1 or 2 records in the emp table, then show only one otherwise show two - do you think it could be anything different? As you say though, clarification from the OP would be of benefit! – Vérace Apr 10 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.