I’m trying to write a query that looks at an “attendance table” for a given date (such “Last Week”) to see if a person has attended to a certain location in the past or if they are a first timer.
The table “Attendance” can be represented like this as having 5 rows:
Row# Person_ID Location Date of attendance Comment
1 John A Any date in past John has attended twice in the past
2 John A Any date in past but different from his first attendance
3 Bob A Any date in past Bob has attended once in the past so should be
ignored by the query.
4 Dave A LAST Week Dave has never attended in the past and needs
to be returned in a query
5 Bob A LAST Week
So I need a query just to return ‘Dave’
This is my (failed) attempt:
Select *
from Attendance A
Left Join Attendance B --creating a self join
Where a.Person_ID = b.Person_ID
AND a.Location = b.Location
And a.DateOfAttendance = 'Monday_Last_Week’ AND 'Sunday_Last_Week’ – this criteria is necessary as the query will be run once a week on any new attenders to location A checking to see if any of them are first timers.
AND a.DateOfAttendance <> b.DateOfAttendance
-- without the final criteria below this returns the repeat attenders (John and Bob as it recognises that they have 2 different dates)
and b.DateOfAttendance IS NULL
-- I was (wrongly) expecting NULLS to show for those rows which were first timers and therefore not have a previous date to match on. But it doesn’t of course because I’m restricting the Date of attendance above…
Can anyone enlighten me to the solution please – I’m going around in circles now…
