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" "Bob has attended in the past so should be ignored by the query."
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 is between '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…
[2]: /answers/storage/attachments/125510-table.png