For example, my table A is work_schedule:
| Employee_id | Week_start | Work_schedule |
|---|---|---|
| A | 2021-01-03 | Day shift |
| A | 2021-01-10 | Day shift |
| A | 2021-01-17 | Night shift |
| B | 2020-12-27 | Day shift |
| B | 2021-01-03 | Day shift |
Table B is employee_history:
| Employee_id | Calendar_date | Tenure |
|---|---|---|
| A | 2020-12-20 | 0 |
| A | 2020-12-21 | 1 |
| A | --- | 2-30 |
| A | 2021-01-19 | 31 |
| A | 2021-01-20 | 32 |
| B | 2020-12-15 | 0 |
| B | 2020-12-16 | 1 |
| B | --- |
Employee can choose work schedule 2 weeks ahead, and I want to fetch tenure at the snapshot date (2 weeks ahead). For employee A, the 14 days time period can match a calendar_date. But for employee B, he started within 2 weeks. I want to have the closet date to the 2-week date.
The ideal output is:
| Employee_id | Week_start | Work_schedule | Calendar_date (to calculate tenure) | Tenure (at 2 weeks ago) |
|---|---|---|---|---|
| A | 2021-01-03 | Day shift | 2020-12-20 | 0 |
| A | 2021-01-10 | Day shift | 2020-12-27 | 7 |
| A | 2021-01-17 | Night shift | 2021-01-03 | 14 |
| B | 2020-12-27 | Day shift | 2020-12-15 | 0 |
| B | 2021-01-03 | Day shift | 2020-12-20 | 5 |
For one record to fetch closet date, I can use
order by abs(datediff(day, (week_start - 14), calendar_date)) asc
limit 1
For example, fetch ‘2020-12-15’ as the closest date to ‘2020-12-13’.
select employee_id, calendar_date, tenure
from employee_history h
where employee_id = B
order by abs(datediff(day, ('2020-12-27' - 14), date_key)) asc
limit 1
But I have more than one employees in this situation, how can I get the closest calendar_date for all those that cannot find a match for exactly 2 weeks?