SQL get employees who salary increased more than $1000 from 1999 to 2000-CodePudding

Here is a table for salary table: emp_id, salary, from_date, to_date.

from_date contains the date information about when a new salary starts, to_date contains the date information about when a new salary end.

e.g.

emp_id, salary , from_date,  to_date.
100,   1000,     2020-01-01, 2021-01-01
100,   2000,     2021-01-01, 2022-01-01

this person's salary from 2020 to 2021 is 1000. and from 2021 to 2022, the salary became 2000 instead.

This is what I have so far, can someone double-check since something seems off?

Thanks in advance.

SELECT PrevSalaries1.emp_no, (CurrSalaries2.salary - PrevSalaries1.salary) as salarie_add
FROM 
    (
    SELECT emp_no, salary
    FROM salaries s1 
    WHERE YEAR(s1.to_date) = '1999' 
    GROUP BY s1.emp_no
    HAVING s1.salary = min(s1.salary)
    )  PrevSalaries1
 JOIN
    (
    SELECT emp_no, salary
    FROM salaries s2
    WHERE s2.to_date >= '2000-01-01' and s2.from_date < '2000-01-01'
    GROUP BY s2.emp_no
    HAVING s2.salary = max(s2.salary)
    ) CurrSalaries2
WHERE PrevSalaries1.emp_no = CurrSalaries2.emp_no
AND CurrSalaries2.salary - PrevSalaries1.salary > 1000;

this is link for code:

update1: I think its important to mention that people salary maybe change (increase or decrease)multiple times in a year. That is why i use min to get lowest salary in 1999. and highest salary in 2000. Hope this helps.

CodePudding user response：

WITH prev AS 
    (
    SELECT emp_no, salary
    FROM frbi_exam.salaries s1 
    WHERE YEAR(s1.to_date) = '2000' 
    ),
curr AS
    (
    SELECT emp_no, salary
    FROM frbi_exam.salaries s1 
    WHERE YEAR(s1.to_date) = '2001' 
    )
SELECT curr.emp_no, curr.salary AS current_sal, prev.salary AS prev_sal
FROM curr
LEFT JOIN prev ON curr.emp_no = prev.emp_no
WHERE curr.salary - prev.salary > 1000

curr CTE is for current state of salary, prev is for previous state. Then you only need to subtract one from another.

Of course this is on assumption that you only care for increase of salary - thus the LEFT JOIN (people who left the company are not in current salary anymore). Otherwise you could use FULL OUTER JOIN and check the difference.

CodePudding user response：

SELECT emp_no, salary  
FROM frbi_exam.salaries s1  
WHERE YEAR(s1.to_date) = '1999'  
GROUP BY s1.emp_no  
HAVING s1.salary = min(s1.salary)

This is just not going to work at all because your HAVING clause references a field that isn't in your GROUP BY clause. Your select doesn't make sense. Select what you want. Want the emp_no and the minimum salary, right? So that's what you select. Aggregate functions (min, max, total, etc) compare all the values passed to them (salary, in this case) for each of the group by fields (emp_no) in this case. So what you want is this

SELECT emp_no, min(salary) as MinSalary  
FROM salaries s1  
WHERE YEAR(s1.to_date) = '1999'  
GROUP BY s1.emp_no

I'll let you figure out how to fix the other sub query.

CodePudding user response：


SELECT curr.emp_no, (curr.CurrSalary - prev.PrevSalary) AS Salary_Increment
FROM
(   SELECT emp_no, max(salary) as CurrSalary  -- Current Max salary
    FROM salaries s1  
    WHERE YEAR(s1.to_date) = '2000'  
    GROUP BY s1.emp_no
    ) AS curr
LEFT
(   SELECT emp_no, max(salary) as PrevSalary  -- Previous year Max salary
    FROM salaries s1  
    WHERE YEAR(s1.to_date) = '1999'  
    GROUP BY s1.emp_no
    ) AS prev
    ON curr.emp_no = prev.emp_no
WHERE 
    (curr.CurrSalary - prev.PrevSalary) > 1000  -- Difference is more than 1000