Algorithm for projecting benefits

[Ender]

I've got a project where the goal is to figure out when benefits (Dental Insurance, Medical Insurance, Life Insurance) will be available to new employees. The business rules say employees are eligible for benefits after 90 calendar days from the Start Date, but only if there are no absences in that period. If there's an absence, the clock starts over and the employee would have to wait until another 90 days to pass without absence.

If a new employee is absent on day 40, they would not be eligible again until day 130. If that same employee were absent on day 135 (after a 90 day period had elapsed without absences), they would still be eligible as of day 130.

The structure has an Employee table, a Payperiod table, and an Hours table (each Hours record is about an activity for a particular date and the hours for that activity). An "activity" could be various types of work, or "Absence".

What I'm trying to figure out is how to make an algorithm to report the Projected Benefits Eligibility Date (or Actual Benefits Eligibility Date, if the date has passed). I know I can filter a relationship to Hours by "Absence", to get the last absence for that employee. But I'm a little mixed up on how to show projected eligibility dates if the date has not passed, or the actual eligibility date if it has (and don't look at absences after the eligibility date).

I'm not sure how much of this can be done via calculation, but since it's tied to a report, a scripted solution is acceptable.

Attached is an example timeline.

Brain-power is appreciated.

[comment]

Would this restatement of the rules be correct?

An employee is eligible for benefits:

(a) if 90 days have elapsed between StartDate and the first absence date;

or

( if 90 days have elapsed between any two consecutive absence dates.

If none of these conditions is true, the employee is expected to be eligible for benefits 90 days after the last absence date.

[Ender]

This may be more succinct (I'm not sure if it's clearer):

"The first date when 90 consecutive days have elapsed (since the Start Date) without absence."

But your version is probably easier to translate into code. I'd adjust it a bit:

An employee is eligible for benefits:

(a) when 90 days have elapsed between Start Date and the first absence date;

or

(when 90 days have elapsed between two consecutive absence dates.

If none of these conditions is true, the employee is expected to be eligible for benefits 90 days after the last absence date or Start Date, whichever is later.

Thanks.

[comment]

I don't get the last correction (in italics) how can StartDate be later than any absence date?

I would also separate the IF from the WHEN, I think. You could put StartDate and all absence dates in a stack, then examine each consecutive pair for a 90 days gap. Once you've found such gap, you output the first member of the pair. If no gap is found, you output the last date in the stack. The benefits then start (or will start) on the output date + 90.

[Genx]

I think the idea is better expressed as:

Where 90 days have elapsed between two consecutive absence dates, or 90 days have elapsed between the start of employment and the first absence date, whichever is earlier.

[comment]

I see (maybe). But that's just saying in another way that - for the purposes of this algorithm - StartDate is the first absence date.

[Genx]

Actually, thats simpler, but how does it work in a relational context - wouldn't you likely have the startDate in an employee table and an absences table with only actual absent dates, or would you simply enter the startDate as the first absence date and count that as the start of employment?

[comment]

I wouldn't do this in a relational context. I would simply feed something like:

StartDate & ¶ & List ( Absences::Date )

to either a custom function or a looping script - I suspect the latter, because you only want to keep computing this for ineligible employees. Once the result ≥ CurrentDate, you would probably want to store it.

[Genx]

Hmmm, that makes it a lot simpler -- Was trying to do it via relationships, but its too hard. Just another tip from you I'll add to my mental list of possible solutions to problems.

Thanks.

[Ender]

Hey, thanks guys. I'll meditate on this a bit, and see how it fits. Sounds sound. :o