Need to find employees at certain age during a specific time period

[kime1974]

I am currently using Crystal XI.

I need to pull employees who are between the age of 55 and 64.99 between the time period of 7/1/2009-6/30/2011. If they reach this age at any time or go over 64.99 during any time during this time period, I will need them on my report.

Thanks!

 

[kray4660]

The only thing I can think of is to check the age at the limits (7-1-09 and 6-30-11). I am assuming you have a birthdate data field.

 

[fisheromacse]

I think Kray is onto the right path.

I think you could use 3 formulas to make this happen:

1. //{@Age01}
datediff('y',{table.birthdate},#07/01/2009#)/365

2. //{@Age02}
datediff('y',{table.birthdate},#06/30/2011#)/365

3. //{@Over55}
IF {@Age01}>=55 OR {@Age02}=>=55
THEN "TRUE"
ELSE "NOT"


place these formula in your details section, then in the section expert, choose suppress x+1 and enter the formula
{@Over55}="NOT"
this will suppress all the records which are not 'TRUE' for age over 55.

 

[kime1974]

The age limits are at between 7/1/2009-6/30/2011. If they are between 55-64.99 at any point during this time period, I need them to show. Each month needs to be checked and if they go over 64.99 during this time period, I would still want them to appear. If they are not 55 on 7/1/2009 but turn that age during 7/1/2009-6/30/2011 I would need them to appear. Does that make sense?

 

[briangriffin]

I'm not positive about who you want to include in the report, so fisherromacse might have the better approach, but one formula might do it:

Age as of 7/1/2009:

datediff("d",{Employee.Birth Date},date(2009,7,1)) /365.25


To get employees who were between 55 and 64.99 during the period:

{@AgeFormula} in 53 to 62.99


To get employees who went over 64.99 during the period:

{@AgeFormula} in 63 to 64.98


So your selection formula would be:

{@AgeFormula} in 53 to 62.99 OR
{@AgeFormula} in 63 to 64.98

if you want to exclude the other records instead of suppressing them.

 

[kime1974]

I know I probably did this the long way, but I made a formula for each month in the time period (24 of them!) and put them in my select expert like suggested above with an or for each month:

{@AgeFormula} in 55 to 64.99

 

[lbass]

If you use Ken Hamady's formula for age, you can create two age formulas, one for 7/1/2009 and one for 6/30/2011, by replacing {ages.DateAnn} below with one of those two dates:

WhileReadingRecords;
DateVar Birth:= {ages.Birth}; // Replace this with your field for Date Of Birth
DateVar Ann := {ages.DateAnn}; // Replace this with date(2009,7,1) or date(2011,6,30)
if (Month(Ann) * 100) + Day (Ann) >=(Month(Birth) *100) + Day (Birth)
then Year (Ann) - Year(Birth)
else Year (Ann) - Year(Birth) -1

Then use a record selection formula like this:

{@ageat07012009} < 65 and
{@ageat06302011} >= 55

-LB

 

[kime1974]

I have to check each month to see if they made the criteria. If they meet the age 55-64.99 at anytime during this time period, I need them to appear on the report. It's not set to 55 for 7/1/09 and 64.99 for 6/30/11, it needs to be evaluated every month.

 

[fisheromacse]

why does it need evaluated every month?
If they met the criteria of being over 55 on 7/1/2009 or on 8/2/09 or on 6/15/2010 or on 6/29/2011, won't they all be over 55 on 6/30/2011??

Is there some piece you have not shared? does this need to be a month-by-month breakdown of the list?

In your first post you said "If they reach this age at any time or go over 64.99 during any time during this time period, I will need them on my report." Is that accurate? if so, why worry about a top age at all?

All that said, I think lbass' presented the most concise solution.

 

[scdmh]

I would use three formulas:

Formula 1 - (Date(2009,07,01) - {table.dob})/365.25
Formula 2 - (Date(2011,06,30) - {table.dob})/365.25
Formula 3 - {@Formula 2} - ((Date(2011,6,30) - Date(2009,7,1))/365.25)

(I'm sure there are other ways to write this but the first one will determine the person's age at your earliest date, the second one will determinw the person's age at the latest date.

The third formula determines whether someone will be 65 or older during your date range.)

The record select is:
({Formula 1} >= 55 and {Formula 2} < 65) or
{Formula 3} >= 64.999

 

[lbass]

My suggestion was that they had to be at least 55 sometime before 6/30/11 AND under 65 sometime on or after 7/1/09. If they meet both these criteria, you should get the correct set of records. No need to check monthly.

-LB