Recursive CTE for leave accrual projections

I have two tables

1. Leave_policy:

2. Empl_Leave:

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:

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 tried with a recursive CTE.

See example:

I will create Accrual calendar. This calendar table can be created separately as a temporary or permanent table, with corresponding indexes. Especially if such calculations are regular. This will simplify the query and improve performance.

Test data. Added LT3 and users 905,906 for test Leave_Rate_Per_Every_Accural,From_Months and Annual_Max_CarryOver changes.

create table Leave_policy(Leave_Type_Id varchar(3), Leave_Rate_Per_Every_Accural int
     ,From_Months int,  Annual_Max_CarryOver int);
insert into Leave_policy values
 ('LT1',    3,  0,  72)
,('LT1',    5,  24, 120)
,('LT2',    4,  0,  96)
,('LT2',    6,  24, 144)
,('LT3',    1,  0,  96)
,('LT3',    2,  24, 144)
,('LT3',    3,  28, 144)
,('LT3',    4,  32, 144)
,('LT3',    5,  36, 144)
;
create table Empl_Leave(id int identity,EMP_ID int, LEAVE_BALANCE float, Leave_Type_ID varchar(3)
     ,Hire_date date,   Last_Accrual_Date date);
insert into Empl_leave(emp_id,leave_balance,Leave_type_id,Hire_date,Last_Accrual_date) values
 (701,  68,     'LT2','10/1/2002','7/27/2024')
,(905,  110,    'LT2','11/2/2022','7/27/2024')
,(906,  110,    'LT3','05/3/2022','7/27/2024')
;

Query

-- version for SQL Server 2019
-- generate series replaced by recursive series generator
-- and greates(...) by case when ...
with params as( -- query parameters
  select startDt  -- calculation start date
    ,datefromparts(year(startDt)+1,1,1) nyDt  -- next new year
    ,dateadd(month,6,startDt) projDt  -- projection date 6 month after startDt
  from (select max(Last_accrual_date) startDt from Empl_Leave) sDt
)
,series as (
      select dateadd(week,2,startDt) Nad -- Next_accrual_date
          ,dateadd(week,26*2,startDt) endDt
      from params
      union all
      select dateadd(week,2,r.Nad) Nad, endDt
      from series r
      where r.Nad<=endDt
)
,AccrualCalendar as( -- Accrual calendar to year
select Nad  -- Next accrual dates in calendar
from(
  select *,row_number()over(partition by year(Nad),month(Nad) order by Nad)rn
  from series 
    -- (
    --   select dateadd(week,n*2,startDt) Nad -- Next_accrual_date
    --   from  (select value n from generate_series(1,26)) wn
    --  cross apply params
    --  )a
  )b
  where rn<=2 -- take only 2 date for every months
)
,lp as( -- Leave_policy with To_Months
  select *,lead(From_months,1,999999)
             over(partition by Leave_Type_Id order by From_months)-1 To_Months
  from Leave_policy
)

main part

,empDates as( -- employee params and dates, including next hire Anniversary
select *
  ,case when nyDt>projDt then
    case when nyDt>haDt then 
      case when haDt>projDt then haDt else projDt end
    else haDt end
   else 
    case when projDt>haDt then projDt else haDt end 
   end lastDt
from(
select id,EMP_ID,LEAVE_BALANCE lb,  Leave_Type_ID lt, Hire_date hdt
  , startDt,nyDt,projDt
  ,case when datefromparts(year(startDt),month(Hire_date),day(Hire_date))<startDt
       then datefromparts(year(startDt)+1,month(Hire_date),day(Hire_date))
   else datefromparts(year(startDt),month(Hire_date),day(Hire_date))
   end haDt -- next hire Anniversary
from Empl_Leave el
cross apply params
  )t
)
,allEmpDates as( -- all dates for employee -- accrual,next hire Anniversary
  -- new year,projection
 -- Accruals
select ed.*,'ac' as sk,Nad as pointDt,datediff(month,hDt,Nad) pointM
from empDates ed
left join AccrualCalendar ac on Nad between startDt and lastDt -- greatest(haDt,nyDt,projDt)
  -- Control points
union all
select * 
  ,case when sk='ny'then nyDt 
          when sk='proj'then projDt
          when sk='ha'then haDt 
     end pointDt
  ,datediff(month,hDt,case when sk='ny'then nyDt 
          when sk='proj'then projDt
          when sk='ha'then haDt 
     end) pointM
from  empDates
left join (values('ha'),('ny'),('proj'))t(sk)
     on sk in('ha','proj')
         or (sk='ny' and projDt>=nyDt)
  )
,balance1 as(-- rolling sum - balance for every date
select ad.* 
  ,sum(case when sk='ac' then Leave_Rate_Per_Every_Accural else 0 end)
    over(partition by emp_id order by pointDt)
  +lb currentB
  ,case when sk='ac' then Leave_Rate_Per_Every_Accural else 0 end leaveQty
  ,From_Months,To_Months,Annual_Max_CarryOver maxCarry
from allEmpDates  ad
left join lp on lp.Leave_Type_id=ad.lt
  and lp.From_Months<=pointM and lp.To_Months>=pointM
)
,AccrualsNYover as( -- correction for max carry - Annual_Max_CarryOver
select  *
  ,case when sk='ny' and currentB>maxCarry then maxCarry-currentB
   else 0 end newyearCorrection
from balance1
)
,balance2 as( -- rolling balance with correction (max carry over)
select *
from(
select * 
  ,sum(case when sk='ac' then leaveQty else 0 end
       +case when sk='ny' then newyearCorrection else 0 end)
    over(partition by emp_id order by pointDt) 
  +lb as CurrentBalance
from AccrualsNYover
)t 
 where sk in('ny','ha','proj') -- after balance calculation take only Control points
)
select EMP_ID,min(lt) Leave_type_id,min(hdt)HireDate
  ,min(startDt) Last_Accrual_date,min(lb) Leave_balance
  ,max(case when sk='proj' then CurrentBalance end) projBalance
  ,max(case when sk='ha' then CurrentBalance end) Hire_Anniversary_Balance
  ,max(case when sk='ny' then CurrentBalance end) CarryOver_Balance
 ,max(nyDt)NewYearDt,max(projDt)ProjectionDate,max(haDt)Hire_Anniversary_date
from balance2
group by emp_id
order by emp_id 

Query for 2022

-- version for SQL Server 2022
with params as( -- query parameters
  select startDt  -- calculation start date
    ,datefromparts(year(startDt)+1,1,1) nyDt  -- next new year
    ,dateadd(month,6,startDt) projDt  -- projection date 6 month after startDt
  from (select max(Last_accrual_date) startDt from Empl_Leave) sDt
)
,AccrualCalendar as( -- Accrual calendar to year
select Nad  -- Next accrual dates in calendar
from(
  select *,row_number()over(partition by year(Nad),month(Nad) order by Nad)rn
  from(
      select dateadd(week,n*2,startDt) Nad -- Next_accrual_date
      from  (select value n from generate_series(1,26)) wn
      cross apply params
     )a
  )b
  where rn<=2 -- take only 2 date for every months
)
,lp as( -- Leave_policy with To_Months
  select *,lead(From_months,1,999999)
             over(partition by Leave_Type_Id order by From_months)-1 To_Months
  from Leave_policy
)
,empDates as( -- employee params and dates, including next hire Anniversary
select id,EMP_ID,LEAVE_BALANCE lb,  Leave_Type_ID lt, Hire_date hdt
  , startDt,nyDt,projDt
  ,case when datefromparts(year(startDt),month(Hire_date),day(Hire_date))<startDt
       then datefromparts(year(startDt)+1,month(Hire_date),day(Hire_date))
   else datefromparts(year(startDt),month(Hire_date),day(Hire_date))
   end haDt -- next hire Anniversary
from Empl_Leave el
cross apply params
  )
,allEmpDates as( -- all dates for employee -- accrual,next hire Anniversary
  -- new year,projection
select ed.*,'ac' as sk,Nad as pointDt,datediff(month,hDt,Nad) pointM
from empDates ed
left join AccrualCalendar ac on Nad between startDt and greatest(haDt,nyDt,projDt)
union all
select * 
  ,case when sk='ny'then nyDt 
          when sk='proj'then projDt
          when sk='ha'then haDt 
     end pointDt
  ,datediff(month,hDt,case when sk='ny'then nyDt 
          when sk='proj'then projDt
          when sk='ha'then haDt 
     end) pointM
from  empDates
left join (values('ha'),('ny'),('proj'))t(sk)
     on sk in('ha','proj')
         or (sk='ny' and projDt>=nyDt)
  )
,balance1 as(-- rolling sum - balance for every date
select ad.* 
  ,sum(case when sk='ac' then Leave_Rate_Per_Every_Accural else 0 end)
    over(partition by emp_id order by pointDt)
  +lb currentB
  ,case when sk='ac' then Leave_Rate_Per_Every_Accural else 0 end leaveQty
  ,From_Months,To_Months,Annual_Max_CarryOver maxCarry
from allEmpDates  ad
left join lp on lp.Leave_Type_id=ad.lt
  and lp.From_Months<=pointM and lp.To_Months>=pointM
)
,AccrualsNYover as( -- correction for max carry - Annual_Max_CarryOver
select  *
  ,case when sk='ny' and currentB>maxCarry then maxCarry-currentB
   else 0 end newyearCorrection
from balance1
)
,balance2 as( -- rolling balance with correction (max carry over)
select *
from(
select * 
  ,sum(case when sk='ac' then leaveQty else 0 end
       +case when sk='ny' then newyearCorrection else 0 end)
    over(partition by emp_id order by pointDt) 
  +lb as CurrentBalance
from AccrualsNYover
)t 
where sk in('ny','ha','proj')
)
select EMP_ID,min(lt) Leave_type_id,min(hdt)HireDate
  ,min(startDt) Last_Accrual_date,min(lb) Leave_balance
  ,max(case when sk='proj' then CurrentBalance end) projBalance
  ,max(case when sk='ha' then CurrentBalance end) Hire_Anniversary_Balance
  ,max(case when sk='ny' then CurrentBalance end) CarryOver_Balance
  ,max(nyDt)NewYearDt,max(projDt)ProjectionDate,max(haDt)Hire_Anniversary_date
from balance2
group by emp_id
order by emp_id 

Output before grouping

fiddle

The case has not been verified when the new year and the date of accruals coincide.