Left Anti Join in SQL

Jason_2021 1 Reputation point
2021-08-19T11:47:15.43+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.
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…

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,914 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

3 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-08-20T03:06:42.06+00:00

    Hi @Jason_2021 ,

    Welcome to Microsoft Q&A!

    For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

    Please refer below example and check whether it is helpful to you.

    create table Attendance  
    (Person_ID varchar(10),  
    Location varchar(10),  
    DateOfAttendance date)  
      
    insert into Attendance values  
    ('John','A','2021-05-01'),  
    ('John','A','2021-06-01'),  
    ('Bob','A','2021-07-01'),  
    ('Dave','A','2021-08-12'),  
    ('Bob','A','2021-08-13')  
    
    SET DATEFIRST 7;   
    select a.Person_ID  
    from Attendance a  
    inner join   
    (select * from Attendance  
    where DateOfAttendance between DATEADD(wk, -1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))   
    AND DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))) b  
    on a.Person_ID=b.Person_ID and a.Location=b.Location  
    group by a.Person_ID,a.location  
    having count(a.DateOfAttendance)=1  
    

    Output:

    Person_ID  
    Dave  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. MelissaMa-MSFT 24,176 Reputation points
    2021-08-24T01:28:04.637+00:00

    Hi @Jason_2021 ,

    Thanks for your update.

    The original query and sample data were based on one date last week, so it did not work on one day this week.

    "where DateOfAttendance between '12-Aug-2021' and '13-Aug-2021'"

    Based on above requirement, you could refer below updated one and check whether it is helpful.

    SET DATEFIRST 1; --SET DATEFIRST to 1(Monday),you could change to other value if necessary  
      
     declare @startdate date='2021-08-12';  
     declare @enddate date='2021-08-13';  
      
     select a.Person_ID  
     from Attendance a  
     inner join   
     (select * from Attendance  
     where DateOfAttendance between @startdate  
     AND @enddate) b  
     on a.Person_ID=b.Person_ID and a.Location=b.Location  
     group by a.Person_ID,a.location  
     having count(a.DateOfAttendance)=1  
    

    If above is still not working, please provide your own sample data and expected output so that we could proceed to update this query.

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  3. MelissaMa-MSFT 24,176 Reputation points
    2021-08-25T01:42:21.24+00:00

    Hi @Jason99 ,

    Please refer below updated one and check whether it is working.

    SET DATEFIRST 1;   
          
    declare @startdate date='2021-08-12';  
    declare @enddate date='2021-08-13';  
       
    ;with cte as (    
    select a.*  
    from Attendance a  
    inner join   
    (select * from Attendance  
    where DateOfAttendance between @startdate  
    AND @enddate) b  
    on a.Person_ID=b.Person_ID and a.Location=b.Location)  
    select * from  Attendance   
    where Person_ID in (select Person_ID from cte     
    group by Person_ID,location  
    having count(DateOfAttendance)=1)  
    

    Output:

    Person_ID	Location	DateOfAttendance  
    Dave	A	2021-08-12  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.