I have a table (Empl_Leave) with EMP ID, Current Leave balance, Leavetype ID, Hiredate, Last_Accrual_Date. And in another table(Leave_policy) i have Leavecode ID, Leave_Rate_Per_Every_Accrural, Months_Until_Range, Annual_Max_Carryover, and they look like this
Leave_policy

|Leave_Type_Id |Leave_Rate_Per_Every_Accural| Months_Until_Range| Annual_Max_CarryOver|

|LT1| 3| 0| 72|

|LT1| 5| 24| 120|

|LT2| 4| 0| 96|

|LT2| 6| 24| 144|

Empl_Leave

|EMP_ID| LEAVE_BALANCE| Leave_Type_ID| Hire_date| Last_Accrual_Date|

|701| 68| LT2| 10/1/2002| 7/27/2024 | 7/27/2024|

|702| 85| LT1| 11/12/1996| 7/28/2024 | 7/27/2024|

|703| 230| LT3| 9/8/2007| 7/29/2024 | 7/27/2024|

|704| 129| LT1| 3/4/2012| 7/30/2024 | 7/27/2024|

|705| 187.5| LT2| 6/1/2008| 7/31/2024 | 7/27/2024|

[LeavePolicy](https://i.sstat ic.net/nSID2axP.png)
Emp_Leave

Now my goal is to get a projections for two factors,

1. for next 6 months from last accrual date, provided two accruals per month and to calculate leave rate units based on Leave_Policy table ( kind of total balance until the sixth month date )
2. In another calculated column i would like to achieve the same above kind of calculation but until employee’s next hire anniversary.
   But if the projection date crosses to next year in the calculation then Annual_Max_Carry_Over must be carry forward and continue the calculation until end of the desired target date.
   Kind of like this output

However i try with recursive CTE – i couldn’t , i gave up trying , any help on this would be appreciated.