I have two tables
1)Leave_policy:
| Leave_Type_Id | Leave_Rate_Per_Every_Accural | Months Until | Annual_Max_CarryOver |
|---|---|---|---|
| LT1 | 3 | 0 | 72 |
| LT1 | 5 | 24 | 120 |
| LT2 | 4 | 0 | 96 |
| LT2 | 6 | 24 | 144 |
2)Empl_Leave:
| EMP_ID | LEAVE_BALANCE | Leave_Type_ID | Hire_date | Last_Accrual_Date |
|---|---|---|---|---|
| 701 | 68 | LT2 | 10/1/2002 | 7/27/2024 |
| 702 | 85 | LT1 | 11/12/1996 | 7/27/2024 |
| 703 | 230 | LT3 | 9/8/2007 | 7/27/2024 |
| 704 | 129 | LT1 | 3/4/2012 | 7/27/2024 |
| 705 | 187.5 | LT2 | 6/1/2008 | 7/27/2024 |
My goal is get employee leave projections ( Current leave balance + LeaveRate * Number of Accruals ) for the next six months from last accrual date and also leave projections to employee hire anniversary.
Something like this:
| Empl_ID | Current_Balance | For next six Month balance | For Until Employee Hire Anniversary |
|---|---|---|---|
| 701 | 68 | (68+Running total until 6 months) | (68 + Running total until Hire anniversary date) |
| 702 | 85 | (85+RunningTotal until 6 months) | (85+Running total until Hire anniversary date) |
Employee have bi weekly leave accruals as only two per month, Months_until and LeaveType_Id columns define how much Leave_Rate_Per_Every_Accrual will be as in the first table. Months_Until is a tenure period MONTH diff between Leave Accrual Date and Hire_Date.
In between these calculations i have to follow Annual_Max_Carryover if the date crosses next year. I am on SQL 2019.
I try with a recursive CTE.
12