Need help on the SQL query. I am trying to get the Benefit Eligibility Date of my clients. I am currently have 10 employees and each of them has different Hired Dates. They need to have the Benefit Eligibility Date.
The enrollment are either on January or July of every year depending when they are qualify. The qualification is, each employee needs to earn at least 800 hours from the date they hired.
So, these are what I need: (let me give you just two of them as a small example)
Employee_Name Hired_Date Benefit_Eligibility_Date
Anthony 03/02/2011 ?
Melinda Rusnadi 09/15/2008 ?
So, Anthony Benefit_eligibility_Date should be in 07/01/2012 if he earned 800 hours or more and
Melinda Benefit_Eligibility_Date should be in 01/01/2009 if she earned 800 hours or more. Keep in mind that the Benefit enrollment is on every January or July and because Anhtony hired on March, he can enroll in the next enrollment month, which is July. Melinda who hired on September would have to enroll on the next enrollment date, which is on January. Does it make sense?
This is my query:
Select Employee_Name
,Hired_Date
,Case when (Hired_Date > = 800) then .......... end as Benefit_eligibility_Date
From MyEmployTbl
Anyone knows how to finish my case statement so I can have the Benefit_eligibility_Date ?
No not entirely.
Please explain the 800 hours.
How many hours is in your workday? How many days in a week does your employees work? Do you have to take holidays, sickdays ect. into account?
So the 800 hours is the requirement to be able to get the benefit and eligible to enroll. We don't care about of how many days in a week does my employees work, we don't care about holiday, sickday, etc but as long as they made 800 hours and above, they are are qualify to enroll the benefit.
So from one of the above example, Anthony. He hired on 03/02/2011 and let' assume he made 800 hours after a year (which is 365 days) from the date he got hire then he is eligible to enroll the benefit on 07/01/2012 not 01/01/2012 because the enrollment falls on every January and July on each year. Keep in mind that Anthony hired date is 03/02/2011, so a year from 03/02/2011 is 03/02/2012 and because he is qualify (he made 800 hours already), he can enroll on July which is 07/01/2012. Does it make sense?
All of what you said makes sense - except, in your query you have a Hired_Date column but there does not seem to be any column/data to indicate the date on which the employee completed 800 hours. In your example in your second posting, you assumed that the employee completed 800 hours in a year. Is that always the case? If not how do you know when that date is.
If you have some way of determining the date on which the 800 hours was completed, then your query could be like this:
select
EmpName,
HiredDate,
Date800HrsCompleted,
dateadd(mm,datediff(mm,0,Date800HrsCompleted)/6*6,'19000701') as Benefit_eligibility_Date
From MyEmployTbl;