By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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')
-- ,(702, 85, 'LT1','11/12/1996','7/28/2024')
-- ,(703, 230, 'LT3','9/8/2007','7/29/2024')
-- ,(704, 129, 'LT1','3/4/2012','7/30/2024')
-- ,(705, 187.5, 'LT2','6/1/2008','7/31/2024')
,(905, 110, 'LT2','11/2/2022','7/27/2024')
,(906, 110, 'LT3','05/3/2022','7/27/2024')
;
select * from Leave_policy;
select * from Empl_Leave;
| Leave_Type_Id | Leave_Rate_Per_Every_Accural | From_Months | Annual_Max_CarryOver |
|---|---|---|---|
| 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 |
| id | EMP_ID | LEAVE_BALANCE | Leave_Type_ID | Hire_date | Last_Accrual_Date |
|---|---|---|---|---|---|
| 1 | 701 | 68 | LT2 | 2002-10-01 | 2024-07-27 |
| 2 | 905 | 110 | LT2 | 2022-11-02 | 2024-07-27 |
| 3 | 906 | 110 | LT3 | 2022-05-03 | 2024-07-27 |
-- 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
)
| EMP_ID | Leave_type_id | HireDate | Last_Accrual_date | Leave_balance | projBalance | Hire_Anniversary_Balance | CarryOver_Balance | NewYearDt | ProjectionDate | Hire_Anniversary_date |
|---|---|---|---|---|---|---|---|---|---|---|
| 701 | LT2 | 2002-10-01 | 2024-07-27 | 68 | 140 | 92 | 128 | 2025-01-01 | 2025-01-27 | 2024-10-01 |
| 905 | LT2 | 2022-11-02 | 2024-07-27 | 110 | 156 | 140 | 144 | 2025-01-01 | 2025-01-27 | 2024-11-02 |
| 906 | LT3 | 2022-05-03 | 2024-07-27 | 110 | 146 | 175 | 138 | 2025-01-01 | 2025-01-27 | 2025-05-03 |
Warning: Null value is eliminated by an aggregate or other SET operation.
-- 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
| EMP_ID | Leave_type_id | HireDate | Last_Accrual_date | Leave_balance | projBalance | Hire_Anniversary_Balance | CarryOver_Balance | NewYearDt | ProjectionDate | Hire_Anniversary_date |
|---|---|---|---|---|---|---|---|---|---|---|
| 701 | LT2 | 2002-10-01 | 2024-07-27 | 68 | 140 | 92 | 128 | 2025-01-01 | 2025-01-27 | 2024-10-01 |
| 905 | LT2 | 2022-11-02 | 2024-07-27 | 110 | 156 | 140 | 144 | 2025-01-01 | 2025-01-27 | 2024-11-02 |
| 906 | LT3 | 2022-05-03 | 2024-07-27 | 110 | 146 | 175 | 138 | 2025-01-01 | 2025-01-27 | 2025-05-03 |
Warning: Null value is eliminated by an aggregate or other SET operation.
-- output before grouping
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
)
,empDates as( -- employee params and dates, including next hire Anniversary
| EMP_ID | lb | lt | sk | pointDt | currentB | leaveQty | maxCarry | newyearCorrection | CurrentBalance | startDt | hdt | nyDt | projDt | haDt | lastDt | pointM | From_Months | To_Months |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 701 | 68 | LT2 | ac | 2024-08-10 | 74 | 6 | 144 | 0 | 74 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 262 | 24 | 999998 |
| 701 | 68 | LT2 | ac | 2024-08-24 | 80 | 6 | 144 | 0 | 80 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 262 | 24 | 999998 |
| 701 | 68 | LT2 | ac | 2024-09-07 | 86 | 6 | 144 | 0 | 86 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 263 | 24 | 999998 |
| 701 | 68 | LT2 | ac | 2024-09-21 | 92 | 6 | 144 | 0 | 92 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 263 | 24 | 999998 |
| 701 | 68 | LT2 | ha | 2024-10-01 | 92 | 0 | 144 | 0 | 92 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 264 | 24 | 999998 |
| 701 | 68 | LT2 | ac | 2024-10-05 | 98 | 6 | 144 | 0 | 98 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 264 | 24 | 999998 |
| 701 | 68 | LT2 | ac | 2024-10-19 | 104 | 6 | 144 | 0 | 104 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 264 | 24 | 999998 |
| 701 | 68 | LT2 | ac | 2024-11-02 | 110 | 6 | 144 | 0 | 110 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 265 | 24 | 999998 |
| 701 | 68 | LT2 | ac | 2024-11-16 | 116 | 6 | 144 | 0 | 116 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 265 | 24 | 999998 |
| 701 | 68 | LT2 | ac | 2024-12-14 | 122 | 6 | 144 | 0 | 122 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 266 | 24 | 999998 |
| 701 | 68 | LT2 | ac | 2024-12-28 | 128 | 6 | 144 | 0 | 128 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 266 | 24 | 999998 |
| 701 | 68 | LT2 | ny | 2025-01-01 | 128 | 0 | 144 | 0 | 128 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 267 | 24 | 999998 |
| 701 | 68 | LT2 | ac | 2025-01-11 | 134 | 6 | 144 | 0 | 134 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 267 | 24 | 999998 |
| 701 | 68 | LT2 | ac | 2025-01-25 | 140 | 6 | 144 | 0 | 140 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 267 | 24 | 999998 |
| 701 | 68 | LT2 | proj | 2025-01-27 | 140 | 0 | 144 | 0 | 140 | 2024-07-27 | 2002-10-01 | 2025-01-01 | 2025-01-27 | 2024-10-01 | 2025-01-27 | 267 | 24 | 999998 |
| 905 | 110 | LT2 | ac | 2024-08-10 | 114 | 4 | 96 | 0 | 114 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 21 | 0 | 23 |
| 905 | 110 | LT2 | ac | 2024-08-24 | 118 | 4 | 96 | 0 | 118 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 21 | 0 | 23 |
| 905 | 110 | LT2 | ac | 2024-09-07 | 122 | 4 | 96 | 0 | 122 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 22 | 0 | 23 |
| 905 | 110 | LT2 | ac | 2024-09-21 | 126 | 4 | 96 | 0 | 126 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 22 | 0 | 23 |
| 905 | 110 | LT2 | ac | 2024-10-05 | 130 | 4 | 96 | 0 | 130 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 23 | 0 | 23 |
| 905 | 110 | LT2 | ac | 2024-10-19 | 134 | 4 | 96 | 0 | 134 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 23 | 0 | 23 |
| 905 | 110 | LT2 | ha | 2024-11-02 | 140 | 0 | 144 | 0 | 140 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 24 | 24 | 999998 |
| 905 | 110 | LT2 | ac | 2024-11-02 | 140 | 6 | 144 | 0 | 140 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 24 | 24 | 999998 |
| 905 | 110 | LT2 | ac | 2024-11-16 | 146 | 6 | 144 | 0 | 146 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 24 | 24 | 999998 |
| 905 | 110 | LT2 | ac | 2024-12-14 | 152 | 6 | 144 | 0 | 152 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 25 | 24 | 999998 |
| 905 | 110 | LT2 | ac | 2024-12-28 | 158 | 6 | 144 | 0 | 158 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 25 | 24 | 999998 |
| 905 | 110 | LT2 | ny | 2025-01-01 | 158 | 0 | 144 | -14 | 144 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 26 | 24 | 999998 |
| 905 | 110 | LT2 | ac | 2025-01-11 | 164 | 6 | 144 | 0 | 150 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 26 | 24 | 999998 |
| 905 | 110 | LT2 | ac | 2025-01-25 | 170 | 6 | 144 | 0 | 156 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 26 | 24 | 999998 |
| 905 | 110 | LT2 | proj | 2025-01-27 | 170 | 0 | 144 | 0 | 156 | 2024-07-27 | 2022-11-02 | 2025-01-01 | 2025-01-27 | 2024-11-02 | 2025-01-27 | 26 | 24 | 999998 |
| 906 | 110 | LT3 | ac | 2024-08-10 | 112 | 2 | 144 | 0 | 112 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 27 | 24 | 27 |
| 906 | 110 | LT3 | ac | 2024-08-24 | 114 | 2 | 144 | 0 | 114 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 27 | 24 | 27 |
| 906 | 110 | LT3 | ac | 2024-09-07 | 117 | 3 | 144 | 0 | 117 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 28 | 28 | 31 |
| 906 | 110 | LT3 | ac | 2024-09-21 | 120 | 3 | 144 | 0 | 120 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 28 | 28 | 31 |
| 906 | 110 | LT3 | ac | 2024-10-05 | 123 | 3 | 144 | 0 | 123 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 29 | 28 | 31 |
| 906 | 110 | LT3 | ac | 2024-10-19 | 126 | 3 | 144 | 0 | 126 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 29 | 28 | 31 |
| 906 | 110 | LT3 | ac | 2024-11-02 | 129 | 3 | 144 | 0 | 129 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 30 | 28 | 31 |
| 906 | 110 | LT3 | ac | 2024-11-16 | 132 | 3 | 144 | 0 | 132 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 30 | 28 | 31 |
| 906 | 110 | LT3 | ac | 2024-12-14 | 135 | 3 | 144 | 0 | 135 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 31 | 28 | 31 |
| 906 | 110 | LT3 | ac | 2024-12-28 | 138 | 3 | 144 | 0 | 138 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 31 | 28 | 31 |
| 906 | 110 | LT3 | ny | 2025-01-01 | 138 | 0 | 144 | 0 | 138 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 32 | 32 | 35 |
| 906 | 110 | LT3 | ac | 2025-01-11 | 142 | 4 | 144 | 0 | 142 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 32 | 32 | 35 |
| 906 | 110 | LT3 | ac | 2025-01-25 | 146 | 4 | 144 | 0 | 146 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 32 | 32 | 35 |
| 906 | 110 | LT3 | proj | 2025-01-27 | 146 | 0 | 144 | 0 | 146 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 32 | 32 | 35 |
| 906 | 110 | LT3 | ac | 2025-02-08 | 150 | 4 | 144 | 0 | 150 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 33 | 32 | 35 |
| 906 | 110 | LT3 | ac | 2025-02-22 | 154 | 4 | 144 | 0 | 154 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 33 | 32 | 35 |
| 906 | 110 | LT3 | ac | 2025-03-08 | 158 | 4 | 144 | 0 | 158 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 34 | 32 | 35 |
| 906 | 110 | LT3 | ac | 2025-03-22 | 162 | 4 | 144 | 0 | 162 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 34 | 32 | 35 |
| 906 | 110 | LT3 | ac | 2025-04-05 | 166 | 4 | 144 | 0 | 166 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 35 | 32 | 35 |
| 906 | 110 | LT3 | ac | 2025-04-19 | 170 | 4 | 144 | 0 | 170 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 35 | 32 | 35 |
| 906 | 110 | LT3 | ac | 2025-05-03 | 175 | 5 | 144 | 0 | 175 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 36 | 36 | 999998 |
| 906 | 110 | LT3 | ha | 2025-05-03 | 175 | 0 | 144 | 0 | 175 | 2024-07-27 | 2022-05-03 | 2025-01-01 | 2025-01-27 | 2025-05-03 | 2025-05-03 | 36 | 36 | 999998 |