Left anti join query

Question

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…

Answer 1

Try this query:

select Person_ID, [Location]  
from Attendance   
where [Date of attendance]   
    between cast(dateadd(day, -((datepart(weekday, getdate()) + @@DATEFIRST - 1) % 7 - 1), getdate()) as date)  
    and dateadd(day, 6-((datepart(weekday, getdate()) + @@DATEFIRST - 1) % 7 - 1), getdate())  
except  
select Person_ID, [Location]  
from Attendance   
where [Date of attendance]   
    < cast(dateadd(day, -((datepart(weekday, getdate()) + @@DATEFIRST - 1) % 7 - 1), getdate()) as date)  

"Last week" is interpreted as current week (according to GETDATE) between Monday and Sunday. If you or your task need something else, then give details.

See also: https://learn.microsoft.com/en-us/answers/questions/520422/.