finding consecutive days

[macavity]

Hi! I've tied myself into knots again. Can anyone help, or is this just not possible:

I would like to be able to determine whether a person has been assigned to work on five consecutive days. The problem is that several people are assigned each day, to one or more of altogether 48 shifts. So one person may appear 10 times, another person 6 times, etc. in a single day.

The number and identity of people involved isn't static. They are listed in a "people" table.

Their "shifts" are collected in a "day" table with one record for each shift. It contains, among other things, a field for the name and another for the date.

Is there any way to establish whether any person is assigned (on any shift or shifts - that is immaterial) five days in a row? Furthermore, which person that is, and which five days??

Many thanks in advance!

[comment]

It's possible, if you define a self-join of the Shifts table, based on the name (should really be PersonID!) AND a match between a calculation of the previous 4 dates and the Date. Then count the related records in the self-join. A shift that has 4 related records is the fifth consecutive shift for that person (assuming no person is assigned to the same shift twice).

[macavity]

I've tried and I've tried. Unless I've misunderstood, this doesn't work because there are many records per date. Each day is divided into 24 half-hour portions. I've referred to those as shifts for lack of a better word, but in fact the people work a variable number of hours (and are paid accordingly). So on day 1 person A may be assigned to 10 of these half-hour slots, and person B to 6 of them; on day 2 they may have other shifts of different lengths or other people may be assigned. It is very chaotic. That is why I chose to create a record for each half-hour "shift". That adds up to an indeterminate number of records with the same date and same person ID (of course that is what I used, not the actual name...)

A script automatically fills a portal with a repeating field for each day and lots of conditional formatting. Purely to provide a visual impression of the entire month. That would be useful since there's only one record per day - unfortunately I can't find a way to count days per person without creating ad hoc relationships through a global field. But I want the five consecutive day thing to be visible for all persons all the time.

I appreciate your help very much, but I may just have to give up. I've ticked all the other boxes they wanted and I think they'll be satisfied with what I've done, especially in the incredibly short time they've allowed for it.

[comment]

I don't see anything wrong with "ad hoc relationships through a global field" myself - after all, you cannot be possibly interested if Person A worked for five consecutive days sometime two years ago?

Anyway, I believe what I suggested should work even after your clarification - all that's needed is a bit of de-duping: if you have a calculated list of the five dates (this day and four previous ones) in the Shifts table, and you use it as the matchfield to Date in a self-join, you can get a list of related dates by:

List ( Shifts 2::Date )

Now, this list is going to contain many duplicates, since a person may have multiple shifts on the same day. But:

FilterValues ( Calc5Days ; List ( Shifts 2::Date ) )

is going to contain only unique dates, and only a person that worked for five consecutive days is going to have all 5 of of them.

I am sure there are other options as well, but I am not entirely clear on the point-of-view from which you need to examine this.

[macavity]

Thank you so much! It works perfectly, of course.