Accrued Vacation Calculation
-
Please someone help me to construct a formula in sql to calculate the accrued vacation time of based on the chart below, and the future date that is enter by users:
Yr of Service Days Earned/Yr Hrs Earned/Yr Hrs Earn/Paycheck
less than 1 yr 16days 128 hrs 5.33
through 2nd yr 17days 136 hrs 5.67
through 3rd yr 18days 144hrs 6.00
through 4th yr 19days 152hrs 6.33
through 5th yr 20days 160hrs 6.67
through 6th yr 21days 168hrs 7.00
through 7th yr 22days 176hrs 7.33
through 8th yr 23days 184hrs 7.67
through 9th yr 24days 192hrs 8.00
through 10th yr 25days 200hrs 8.33
after 10th yr 26days 208hrs 8.67
For example, an employee was hired on 12/09/01, the current date is 4/15/05, and there is 24paycheck/year. She want to know how many vacation time she will accrue if she enter 3/31/06? If I manually calculate, data will be like this: she has 4 years services, she earns 6.33hr/paycheck. From 4/15/05-12/31/05, there will be 16paychecks. Therefore, 16*6.33 = 101.28. From 1/1/06 to 3/31/06, she has 5yrs of service, she earns 6.67hr/paycheck. From 1/1/06-3/31/06, there will be 6paychecks. Therefore, 6*6.67=40.02. For total, 101.28+40.02 =141.3.
Thanks so much for your help.
-
I'm pressed for time so sending you what logic/concept I could think of off the top of my head - maybe you can use it to "play around" or see if it gives you any ideas.....
DECLARE @EnteredDate SmallDateTime
DECLARE @ActualNumOfYrs Int
DECLARE @FutureYrs Int
DECLARE @NumOfCurrentWks Int
DECLARE @NumOfFutureWks Int
DECLARE @Emp_ID Int
SET @ActualNumOfYrs = SELECT DATEDIFF(year, hiredate, getdate()) FROM Emp_Table
WHERE Emp_ID = @Emp_ID
SET @FutureYrs = SELECT DATEDIFF(year, getdate(), @EnteredDate) FROM Emp_Table
WHERE Emp_ID = @Emp_ID
SET @NumOfCurrentWks = (DATEDIFF(month, @EnteredDate, '12/31/' + year(getdate()) * 2)
SET @NumOfFutureWks = (DATEDIFF(month, '1/1/' + (year(getdate()) + 1), @EnteredDate ) * 2)
select vacation_accrued =
case @ActualNumOfYrs
when <= 1 and @FutureYrs = 0 then 5.33 * @NumOfCurrentWks
when <= 1 and @FutureYrs = 1 then ((5.33 * @NumOfCurrentWks)
+
(5.67 * @NumOfFutureWks))
when <= 1 and @FutureYrs = 2 then ((5.33 * @NumOfCurrentWks)
+
(6.00 * @NumOfFutureWks))
etc. etc.
**ASCII stupid question, get a stupid ANSI !!!** -
Ok this has the limitation that only work for upto 50 years of service
I think it does what you want
-- create table Vac ( YofService int, DaysPerYear int, HoursPerYear int, HoursPerPay dec(9,2) )
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (0 ,16 ,128 ,5.33)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (1 ,17 ,136 ,5.67)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (2 ,17 ,136 ,5.67)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (3 ,18 ,144 ,6.00)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (4 ,19 ,152 ,6.33)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (5 ,20 ,160 ,6.67)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (6 ,21 ,168 ,7.00)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (7 ,22 ,176 ,7.33)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (8 ,23 ,184 ,7.67)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (9 ,24 ,192 ,8.00)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (10 ,25 ,200 ,8.33)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay) values (11 ,26 ,208 ,8.67)
-- insert into Vac ( YofService, DaysPerYear, HoursPerYear, HoursPerPay)
-- select number, 26, 208, 8.67
-- from master..spt_values where type ='P' and number between 12 and 50
Declare
@doh datetime -- date of hire
, @today datetime -- today's date
, @tdate datetime -- target date
, @ldoy datetime -- last date of current year
, @fdoy datetime -- first date of the last year
select -- Enter your values here
@doh = '20011209'
, @today = getdate()
, @tdate = '20060331'
-- Helpers
select @ldoy = cast(cast(Year(@today)as char(4)) + '1231' as datetime)
, @fdoy = cast(cast(Year(@tdate)as char(4)) + '0101' as datetime)
-- Result
select sum(Payed)
from
(
select
(case
when YofService = datediff(year,@doh,@today) -- first record
then
datediff(m,@today,(case when @tdate > @ldoy then @ldoy else @tdate end))
when YofService = datediff(year,@doh,@tdate) -- last record
then
datediff(m,@fdoy,@tdate)+1
else -- middle records
12 -- all months
end)* 2 * HoursPerPay as Payed
from Vac
where
YofService <= datediff(year,@doh,@tdate) -- max amount of splits
and YofService >= datediff(year,@doh,@today) -- min date in span
and (@doh < @today) -- just to protect 🙂
and (@today < @tdate) -- just to protect 🙂
) subquery
hth
* Noel -
Thank you so much Noel. I tested some based on your instruction. It does work for me.
Minh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply