I'm facing issue regarding getting end_date of previous designation based on the start_date of new designation.
Designation 1: House job officer
Designation 2: Medical officer
Designation 3: Doctor
If employee is promoted to next designation end_date will be start_date -1 of new designation only applicable for designation 2 and 3. No end_date for if there is only first designation(House job officer).
I'm making query but having issue on how to complete it.
UPDATE employee
SET end_date = ( SELECT DATE_SUB(start_date, INTERVAL 1 DAY)
Input Table
| id | designation | start_date | end_date |
|---|---|---|---|
| 101 | house job officer | 2022-01-19 | |
| 101 | medical officer | 2022-03-19 | |
| 101 | doctor | 2022-05-02 |
Output Table
| id | designation | start_date | end_date |
|---|---|---|---|
| 101 | house job officer | 2022-01-19 | 2022-03-18 |
| 101 | medical officer | 2022-03-19 | 2022-05-01 |
| 101 | doctor | 2022-05-02 |
Explanation
