-1

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      |
+-------------+----------------+-------------+--------+
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
Goolsy
  • 237
  • 3
  • 14

1 Answers1

3

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 Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Hi John. This is brillant. I can't believe it was that simple. I was playing around using ROWS BETWEEN but just getting more and more confused. I do have some employees where all their records are in the future. Would there be any way of tweaking your code to also include the first record of those cases? No drama if not, you've done more than enough already. Thanks again. – Goolsy Sep 16 '22 at 01:37