Consecutive Events

[farhanisfarhan]

Dear Community,

I have a table containing records such as:

ID Date Name Attendance

01 01/01/2012 ABC Yes

02 01/01/2012 DEF Yes

03 02/01/2012 ABC Yes

04 02/01/2012 DEF No

05 03/01/2012 ABC No

06 03/01/2012 DEF No

07 04/01/2012 ABC No

08 04/01/2012 DEF No

I want to count consecutive absences of a particular name by dates. i.e Has DEF been absent consecutively for 3 days?

I found something on http://www.briandunning.com/cf/69 but I am not able get it implemented.

Please help...

[comment]

It's not quite clear from your example what "consecutive"means. Would DEF be considered "absent consecutively for 3 days" in the following example?

01 01/01/2012 ABC Yes

02 01/01/2012 DEF Yes

03 08/01/2012 ABC Yes

04 08/01/2012 DEF No

05 15/01/2012 ABC Yes

06 15/01/2012 DEF No

07 22/01/2012 ABC Yes

08 22/01/2012 DEF No

09 29/01/2012 ABC Yes

10 29/01/2012 DEF Yes

It would also help to know why you need this - and when (in what context).

[farhanisfarhan]

I am working on an attendance system. Those who are have not attended consecutive 3 or more expected events will be sent a warning e-mail.

In the above example, DEF has not attended 3 expected events consecutively if sorted by date (08, 15, 22). I want him to be listed in the report also reporting how many consecutive he left, (maybe he has left 4 consecutive dates)...

[comment]

Those who are have not attended consecutive 3 or more expected events will be sent a warning e-mail.

Is there a table of the events they are supposed to attend? That would make it easy to get the IDs of n previous events and establish a relationship to their attendance records.

Otherwise you'd need a "cascading" relationship, which could get slow with a large number of records.

[farhanisfarhan]

I have a table "attendance marking" which sends attendance record to another table "core attendance".

"attendance marking" table sends ID, Date, Name, Attendace as fields to the "core attendance" table.

I want report derived from "core attendance" uniquely showing only those names which have consecutively "No" sorted by date in "Attendance" field for 3 or more than 3 times.

[comment]

You haven't answered my question.

[farhanisfarhan]

Is there a table of the events they are supposed to attend? That would make it easy to get the IDs of n previous events and establish a relationship to their attendance records.

Otherwise you'd need a "cascading" relationship, which could get slow with a large number of records.

Yes, there is a table of events. (which records if someone attended or not with unique id number allocated to each person.)

[comment]

Yes, there is a table of events. (which records if someone attended or not

That would be an Attendance table, not an Events table. The Events table would have one unique record for each event.

To put it another way, you cannot tell if someone missed 3 consecutive events unless you have a list of the events they were supposed to attend, alongside a list of the events they did attend.