Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this setup

attendance table:
------------------------------------------
| member_id | attendance_date | event_id |
------------------------------------------

attendance indeces
---------------------------------------------
| p1 (member_id, attendance_date, event_id) |
| p2 (attendance_date, member_id, event_id) |
| member (member_id)                        |
| total (attendance_date, event_id)         |
---------------------------------------------

members table
-------------------------------------------------
| member_id | department_id | registration_date |
-------------------------------------------------

members indeces:
-----------------------------------------------
| PK (member_id)                              |
| registration (registration_date, member_id) |
| department (department_id, member_id)       |
-----------------------------------------------

So basically this is an attendance system. And I need to run reports based on the attendance table. I've figured out much of the queries needed except for the part where I need to know how many absences a member has for a given time. I pretty much know how to query for data that is there. But I do not know how will I query for data that is not there (absences).

I tried this:

SELECT
  attendance_date,
  COUNT(DISTINCT event_id)
FROM
  attendance
WHERE
  WEEKOFYEAR(attendance_date) BETWEEN 27 AND 31
GROUP BY
  WEEKOFYEAR(attendance_date)

which gives me the number of events a member should've been able to attend based on other's attendance record, but the problem is when there is an event with no attendees at all (unlikely) or when there is a newly registered member.

I tried this:

   EXPLAIN SELECT
     m.member_id AS id,
     COUNT(DISTINCT a.event_id)
   FROM
     members AS m
     LEFT JOIN
       attendance AS a
       ON
         m.registration_date < a.attendance_date AND
         WEEKOFYEAR(a.attendance_date) BETWEEN 27 AND 31
   WHERE
     m.department_id = 1

But EXPLAIN tells me that it will scan 4M rows so I know that it is not the way to go. I know that the query is wrong anyways since COUNT(DISTINCT) will give me multiple rows per member. Any thoughts? And any advice on how should I index the database for this query is also appreciated.

EDIT

To be more specific, this is what I want to do with the query:

  1. Get all members of a department
  2. Get the number of events that has a record in the attendance table between a given period but only those events that occurred later than the current member's registration date

When we subtract the total number of attendance records of the member, then we can actually get how many absences the member already has.

I know that part of this problem stems from a fundamental error in the structure of the application (events shouldn't be defined based on attendance records) but these events are recurring and are quite irregular in their occurrence patterns. So to minimize the need for maintenance, I just decided that I derive event information (or at least their count) be based on the attendance since it is very unlikely that an event will have 0 attendees.

share|improve this question
    
Why is EventID column is not part of the Attendance table? Also, the condition m.registration_date < a.attendance_date will always be true so maybe you don't need it. –  Emmad Kareem Aug 10 '12 at 8:42
    
@Emmad: I think it is. There is probably a typo in the table definition. –  ypercube Aug 10 '12 at 9:00
    
@EmmadKareem the left join isn't joined by the member_id column. This is what I want to say in that query "Get the number of events recorded in the attendance table between week 27 and 31 but only those that occurred later than the current member's registration date" –  Rolando Cruz Aug 10 '12 at 9:35
    
Thanks for the explanation, but I think that the registeration date should not be part of the member table. It is part of the intersection between member table and event table(assuming that a member registers for 0,1 or more events and that an event is attended by 0,1, or more members), this table is not currently defined - As the structure stands that registration date can't have a specific meaning since it is not related to a specific event, hence, m.registration_date < a.attendance_date is not accurate. –  Emmad Kareem Aug 10 '12 at 12:05
    
@EmmadKareem The membership date is a one-time only membership that's why I tied it directly to the members table. It's sort of "the day they joined the club" so to speak. Then the member could attend events which will be indicated by the attendance_date in the attendance table –  Rolando Cruz Aug 11 '12 at 2:33
show 1 more comment

1 Answer

up vote 3 down vote accepted

Why are you determining a member's absence through another member's presence?

I guess there must be an events table in your schema. And I would expect it to have a column called something like event_date. It's the event_date column that you should use to determine which events a member could visit (members.registration_date < events.event_date).

After getting the list of members and their respective valid events, you can anti-join it to the attendance table to see which member missed which (or how many) events.

Here's an example query illustrating what I just said. It implements the anti-join using LEFT JOIN + WHERE IS NULL check:

SELECT
  m.member_id,
  e.event_id
FROM       members    m
INNER JOIN events     e ON m.registration_date < e.event_date
LEFT  JOIN attendance a ON m.member_id = a.member_id
                       AND e.event_id  = a.event_id
WHERE a.member_id IS NULL
  AND ... /* some other conditions to filter members and/or events, if necessary */

Or here's another example showing how you could count total events available to a member and those actually attended by him/her:

SELECT
  m.member_id,
  COUNT(e.event_id) AS total_events,
  COUNT(a.event_id) AS attended_events,
FROM       members    m
INNER JOIN events     e ON m.registration_date < e.event_date
LEFT  JOIN attendance a ON m.member_id = a.member_id
                       AND e.event_id  = a.event_id
WHERE /* some conditions as necessary */

Note that in the last example there's no IS NULL check. It's because that query needs to get all the events per member. Missed events are just not counted by the COUNT() function (because the corresponding a.event_id value contains NULL in those cases).

UPDATE

Since, as per your comment, the events are recurring (and thus have no fixed dates), I would suggest adding and using an event_calendar table, populated beforehand either manually or programmatically. Naturally, the events table in the above examples would be replaced with event_calendar.

Alternatively, if maintaining an event calendar is not an option, you could replace events in your queries with

(
  SELECT DISTINCT
    event_id,
    attendance_date AS event_date
    /* or, if they are not purely dates:
    CAST(attendance_date AS date) AS event_date
    */
  FROM attendance
) e

(basically, @ypercube's suggestion with dates thrown in).

share|improve this answer
    
@Rolando: If you want to keep the structure you have and not add an events table (although I can't see a reason for that), you can replace events in all the queries above with (SELECT DISTINCT event_id FROM attendance) –  ypercube Aug 10 '12 at 17:29
    
+1 after i read your answer I understand what the OP is about (and your answer is a solution to the problem) –  miracle173 Aug 10 '12 at 18:27
    
I am not sure if you had a chance to read my comment above regarding the misplacement of the registration date. What do you think? –  Emmad Kareem Aug 11 '12 at 2:22
    
Hi. The problem is that the events are recurring (I asked a similar question at SO and I must have forgotten to mention it here). And recurrence pattern isn't really fixed. At the very least, I can be assured that events happen weekly, some events happen quarterly, some yearly. With an irregular recurrence, I can only define event occurrences through member attendance. –  Rolando Cruz Aug 11 '12 at 2:36
    
@EmmadKareem: I did see your comment about registration_date. My assumption regarding that attribute at the time was along the lines of what Rolando would eventually tell you, i.e. that the date was related to the person's registration as a member, not as an event participant. –  Andriy M Aug 11 '12 at 8:51
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.