| EmployeeID | EffectiveDate | EndDate | Salary | ContractFlag |
|---|---|---|---|---|
| 100 | 01/01/2022 | 12/31/2022 | 10,000 | Completed |
| 100 | 01/01/2023 | 12/31/2023 | 20,000 | Completed |
| 100 | 01/01/2024 | 12/31/2024 | 30,000 | Active |
| 100 | 01/01/2025 | 12/31/2025 | 40,000 | Pending |
| 200 | 01/01/2022 | 12/31/2022 | 25,000 | Completed |
| 200 | 01/01/2023 | 12/31/2023 | 35,000 | Completed |
| 200 | 01/01/2024 | 12/31/2024 | 35,000 | Active |
| 200 | 01/01/2025 | 12/31/2025 | 40,000 | Pending |
| 300 | 01/01/2022 | 12/31/2022 | 10,000 | Completed |
| 300 | 01/01/2023 | 12/31/2023 | 20,000 | Completed |
| 300 | 01/01/2024 | 12/31/2024 | 30,000 | Active |
| 300 | 01/01/2025 | 12/31/2025 | 40,000 | Pending |
| 400 | 01/02/2024 | 12/31/2024 | 50,000 | Active |
| 400 | 01/01/2025 | 12/31/2025 | 60,000 | Pending |
I need to return:
Next lower value(cannot be equal to current active)
| EmployeeID | EffectiveDate | EndDate | Salary | ContractFlag |
|---|---|---|---|---|
| 100 | 01/01/2023 | 12/31/2023 | 20,000 | Completed |
| 200 | 01/01/2023 | 12/31/2023 | 25,000 | Completed |
| 300 | 01/01/2023 | 12/31/2023 | 20,000 | Completed |
| 400 | NULL | NULL | NULL | NULL |
Next higher value(cannot be equal current active)
| EmployeeID | EffectiveDate | EndDate | Salary | ContractFlag |
|---|---|---|---|---|
| 100 | 01/01/2025 | 12/31/2025 | 20,000 | Pending |
| 200 | 01/01/2025 | 12/31/2025 | 25,000 | Pending |
| 300 | 01/01/2025 | 12/31/2025 | 20,000 | Pending |
| 400 | 01/01/2025 | 12/31/2025 | 60,000 | Pending |
I will insert into another table which should look like this:
| EmployeeID | PrevIncreaseDate | NextIncreaseDate | PrevSalary | NextSalary | CurrentSalary |
|---|---|---|---|---|---|
| 100 | 01/01/2023 | 01/01/2025 | 10,000 | 40,000 | 30,000 |
| 200 | 01/01/2022 | 01/01/2025 | 25,000 | 40,000 | 35,000 |
| 300 | 01/01/2023 | 01/01/2025 | 30,000 | 40,000 | 30,000 |
| 400 | NULL | 01/01/2025 | NULL | 60,000 | 50,000 |
I’ve tried using the LAG function, but this isn’t effective if there are repeat salary amounts like example for EmployeeID 200. EmployeeID 200 has a current ‘Active’ Salary of 35,000 with a previous year ‘Completed’ Salary of also 35,000. The next lowest value is 25,000.
I also tried creating a sub-set of records and selecting the MAX or MIN record
SELECT MAX(Salary), i.EmployeeID, i.EffectiveDate FROM [Table] i
WHERE i.Salary < (SELECT MAX(Salary) FROM [Table] f WHERE f.ContractFlag = 'Active')
GROUP BY i.EmployeeID, i.EffectiveDate`
Also note that EmployeeID 400 does not have a previous salary as they started in 2024.
There may be some cases where an Employee does not having a pending contract for next year, and thus, should return null values.
New contributor
SQLDevCA is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.