Find the 'active' row based on current date from date effective rows

Goolsy 0
Viewed 34

I have a table that stores employee information. There can be multiple, date effective, records for a single employee if their working conditions change. For example, moving from full time to part time. Each record has an effective from date, but not an effective to date. Some records may be future dated records indicating a planned change in circumstances.

I would like to identify the record that is 'active' at a given time based on the current date. So in my example below, if we assume the current date is 16th September 2022, I would like to return the 3rd row as it is after August 31 2022 and before January 1 2023.

+-------------+----------------+-------------+--------+
| Employee_ID | Effective_Date | Work_Status | Job_ID |
+-------------+----------------+-------------+--------+
| 1           | 2021-01-01     | FT          | A      |
| 1           | 2021-04-22     | PT          | A      |
| 1           | 2022-08-31     | PT          | B      |
| 1           | 2023-01-01     | FT          | B      |
+-------------+----------------+-------------+--------+
sql-server sql
1 Answers

Assuming you want current position for all employees.

Select top 1 with ties * 
 From  YourTable
 Where Effective_Date<=getdate()
 Order by row_number() over (partition by Employee_ID order by Effective_Date desc)
John Cappelletti0
Related