Need Formula for "1st of month following 30 days" 1

[vlfox]

I'm trying to do a derived field for a Benefit Effective date that is "1st of month following 30 days from date of hire" for Enterprise version.

In PCPW, I had a formula using:
TRUNC("REPORTS"."V_EMPLOYEE"."HIREDATE"+60,'MM')
but there is no TRUNC for Enterprise.

DATEADD for Enterprise results in addition of days or months, but not going forward to the first of the month following.

ThanX for all tips !

 

[ReportSmithing]

If you're on Oracle, you would create a derived field:

TO_DATE((to_char(add_months(last_day("EMP1"."HIRE_DT" - 1),1)+1)))

 

[vlfox]

My apologies; I forgot to clarify that I'm SQL on this platform...ThanX !

 

[CharlesCook]

This would be a strating point for SQL Server, you will need to play around a bit to tune it in.

YOU NEED TO PUT THIS ALL TOGETHER ON 1 LINE FOR IT TO WORK

STEP 1
This will get you the Hiredate plus 30 days.
DATEADD(Day,30,"REPORTS"."V_EMPLOYEE"."HIREDATE")

STEP 2
This will get you the next month after the 30 days
DATEADD(Month,1, !! INSERT FORMULA FROM STEP 1 HERE !!)

STEP 3
Now we need to get to the first day of the month
DATEPART(Day,!! INSERT FORMULA FROM STEP 2 HERE !!)

STEP 4
WE NEED TO WRAP IT ALL UP.
DATEADD(Day,(!! INSERT FORMULA FROM STEP 3 HERE !! * -1),!! INSERT FORMULA FROM STEP 2 HERE !!)

I hope you can follow this.......

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[vlfox]

I only wish you could see how big I'm grinning right now !

Your start was great, just had minor tweaking ! I also appreciate how you broke it out to help me understand where I was going.

You are very much appreciated !!

 

[ReportSmithing]

This would work in most cases...

DATEADD(Day,30,"REPORTS"."V_EMPLOYEE"."HIREDATE")

But what about the month with only 28(29) days in it? Or those hired on the first of February - should they be effective for March 1st? Or those months with 31 days?

I don't know if you're doing some benefit eligibility reporting, but usually when they 'say' the first of the month following 30 days they don't mean an exact 30 days - typically its the first of the month following a month and if hired ON the first, it's the first of the next month.

This should get you the LAST_DAY of the month and you can work it from there..

dateadd(mm,1,"REPORTS"."V_EMPLOYEE"."HIREDATE" - day("REPORTS"."V_EMPLOYEE"."HIREDATE")+1)-1

Let me know if this helps or if I'm totally off base - I usually work on Oracle.