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`
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)
-
+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 '20 at 0:25
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!
-
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 '20 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 '20 at 11:57