Help Calculating Eligibility Date -

[rougex]

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

 

[CharlesCook]

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

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[rougex]

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.

 

[CharlesCook]

You can look at the Oracle Functions Here:

Case:

http://www.techonthenet.com/oracle/functions/case.php

TO_CHAR:

http://techonthenet.com/oracle/functions/to_char.php

LAST_DAY:

http://www.techonthenet.com/oracle/functions/last_day.php

Oracle Date Math:

http://www.praetoriate.com/t_garmany_easysql_dates_and_SQL.htm

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[rougex]

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.