Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help Calculating Eligibility Date -

Status
Not open for further replies.

rougex

Technical User
Nov 7, 2006
19
US
Greetings
Using SQL platform I am trying to calculate the eligibility date for Health Benefits based on an employees Hire Date.

I have tried many derivations of Date Functions and even gone through a few threads here, but it is not working properly

Eligibility Rules
Eligible the first of the month FOLLOWING 90 days of employment, unless the 90th day of employemnt is the first of the month.

So far I have tried :
DATEADD(MM,4,DATEADD(DD,-(DATEPART(DD,PS_EMPLOYEES.HIRE_DT)-1),PS_EMPLOYEES.HIRE_DT))

Any assistance is appreciated
 
This SQL Derived Field works in Oracle. You would need to convert it to SQL-Server.

/* Eligibility Rules */
/* Eligible the first of the month FOLLOWING 90 days of employment, */
/* unless the 90th day of employemnt is the first of the month. */

CASE
/* Is the 90th day of employemnt the first of the month? */
WHEN TO_CHAR(("PS_EMPLOYMENT"."HIRE_DT" + 90), 'dd') = '01'

/* Then return the 90th day of employment */
THEN ("PS_EMPLOYMENT"."HIRE_DT" + 90)

/* Else return the first of the next month FOLLOWING 90 days of employment */
ELSE LAST_DAY(("PS_EMPLOYMENT"."HIRE_DT" + 90)) + 1
END

Specializing in ReportSmith Training and Consulting
 
Thank you for the information!
I know NADA about Oracle so I can do some playing around in the Derived Field box to see if I can get it to work.
 
CASE
WHEN DATEPART(D,DATEADD(DD,90,PS_EMPLOYEES.HIRE_DT)) = 1 THEN DATEADD(DD,90,PS_EMPLOYEES.HIRE_DT)
ELSE DATEADD(DD,(1-DATEPART(D,(DATEADD(MM,1,DATEADD(DD,90,PS_EMPLOYEES.HIRE_DT))))),(DATEADD(MM,1,DATEADD(DD,90,PS_EMPLOYEES.HIRE_DT))))
END


Finally got it to work !! Just in case anyone else struggles with this, the above statement worked for me. Thank you Charles for all your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top