Left anti join query

Jason99 21 Reputation points
2021-08-23T11:10:14.187+00:00

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.

125636-image.png!

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…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,904 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 113K Reputation points
    2021-08-23T11:26:54.067+00:00

    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/.


0 additional answers

Sort by: Most helpful