SQL query to get the employee who has not been appraised

Question

There is a employees salary history table, sal_hist which has empid,name, salary and effective_date. Requirement is to get the employee who has not had a salary increase. Below is the table:

EmpId	name	salary	effective_date
1	a	1000	10-5-2020
1	a	2000	12-6-2020
1	a	3000	12-7-2020
2	b	2500	12-5-2020
2	b	3500	12-7-2020
3	c	2500	12-5-2020

Below is the query I have:

Select id,name from sal_hist group by id,name having count(1)=1;

Is there a different way to achieve the result?

Answer 1

If your date column is the appraisal date and you want the user who has not got an appraisal at the latest date then:

SELECT id, name, salary, appraisal_date
FROM   (
  SELECT s.*,
         MAX(appraisal_date) OVER (PARTITION BY id) AS max_user_appraisal_date,
         MAX(appraisal_date) OVER () AS max_appraisal_date
  FROM   salary_history s
)
WHERE appraisal_date = max_user_appraisal_date
AND   max_user_appraisal_date < max_appraisal_date

Which, for the sample data:

CREATE TABLE salary_history (Id, name, salary, appraisal_date) AS
SELECT 1, 'a', 1000, DATE '2020-05-10' FROM DUAL UNION ALL
SELECT 1, 'a', 2000, DATE '2020-06-12' FROM DUAL UNION ALL
SELECT 1, 'a', 3000, DATE '2020-07-12' FROM DUAL UNION ALL
SELECT 2, 'b', 2500, DATE '2020-05-12' FROM DUAL UNION ALL
SELECT 2, 'b', 3500, DATE '2020-07-12' FROM DUAL UNION ALL
SELECT 3, 'c', 2500, DATE '2020-05-12' FROM DUAL;

Outputs:

ID	NAME	SALARY	APPRAISAL_DATE
3	c	2500	12-MAY-20

db<>fiddle here