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:
,Case when (Hired_Date > = 800) then .......... end as Benefit_eligibility_Date
Anyone knows how to finish my case statement so I can have the Benefit_eligibility_Date ?
Thank you guys