1

I have the following relevant tables:

person: PersonID, FullName, etc.
attendance: PersonID, EventID, AttendDate

I need a query to return all people who are "attending" EventID=1 today but will miss at least one day in the next week, preferably also returning the first day they will miss. (It's not really attendance of an event per se, but EventID=1 means they are traveling and I want to know who is coming back in the next week.) Hunting around SO, I found this answer that helped me get as far as producing a list of the missing dates for a specific person:

SELECT missdate FROM (
  SELECT @r:= DATE_ADD( @r, INTERVAL 1 DAY ) missdate
  FROM ( SELECT @r := CURDATE() ) vars, attendance 
  LIMIT 7
) justdates WHERE justdates.missdate NOT IN (
  SELECT attendance.AttendDate FROM attendance
  WHERE attendance.PersonID = 5222
  AND attendance.AttendDate = justdates.missdate
)

That works like a charm. But if I try to join it with the person table and also add the criteria that they have to be currently on travel (have an attendance record for today), I can't make it work. So far I have this:

SELECT person.PersonID, person.FullName, MIN(home.missdate) AS returndate FROM person
INNER JOIN (
  SELECT pt.PersonID FROM person pt
  INNER JOIN attendance at ON pt.PersonID=at.PersonID
  WHERE at.EventID=1 AND at.AttendDate=CURDATE()
) travel ON travel.PersonID = person.PersonID
INNER JOIN (
  SELECT missdate FROM (
    SELECT @r:= DATE_ADD( @r, INTERVAL 1 DAY ) missdate
    FROM ( SELECT @r := CURDATE() ) vars, attendance 
    LIMIT 7
  ) justdates WHERE justdates.missdate NOT IN (
    SELECT attendance.AttendDate FROM attendance
    WHERE attendance.PersonID = person.PersonID
    AND attendance.AttendDate = justdates.missdate
  )
) home ON home.PersonID = person.PersonID
GROUP BY person.PersonID,person.FullName

But it doesn't recognize person.PersonID in the subquery's WHERE clause. How should I do this?

Community
  • 1
  • 1
OsakaWebbie
  • 645
  • 1
  • 7
  • 21

0 Answers0