Left Anti Join in SQL

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

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…

Answer 1

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.

Answer 2

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.

Answer 3

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.