Calculating 1st of Month from Hire Date

[lademaio]

Hi All,

I hope my question is not to elementary for this forum, but I need some guidance and you all seem to be really helpful and knowledgable.

I need to calculate a participation date, which is the 1st of the month following six months plus the hire date (i.e. Hiredate 06/05/02, six months completed 12/05/02, participation date 01/01/03). The tricky part is if that date falls on the 1st, then that hire plus 6 is the participation date.

I thought I would break the hire date down to Month, Day & Year and then use an if statement to add the months (i.e. IF date is 1st, then add 6 to month, else add 7) Of course I see another potential problem with the year may need to change then, so I guess another IF statement?? :-?

I am not sure if there is an easier or better way - but any help would be sincerely appreciated. Thanks All!

Peace, Loretta

 

[griffindm]

A combination of the functions add-months and first-of-month will give you what you want without having to parse the date down into day, months, and year. The drawback is that this is local processing.

However as this is for participation date calculation for what I assume are employees, there should not be too many rows in the report to start with (compared with a transaction table), so there may not be a noticable performance hit at all.

Try:

add-months(first-of-month(hire-date),6) ...

as your calculation.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[nagrajm]

Try

Participation Date = If (day(Hire_Date) = 1) then (first-of-month(add-months(Hire_Date,6))) else (first-of-month(add-months(Hire_Date,7)))

Note :

Day()
first-of-month()
add_months

are all Cognos supported functions.

Hope that helps.

 

[griffindm]

Good addition nagrajm. I had forgotten about the 'first of month' exception.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[lademaio]

Thanks guys, I really appreciate the help! I knew you all would have a better solution, you are the best! :-D