question

Jason2021-1729 avatar image
0 Votes"
Jason2021-1729 asked MelissaMa-msft commented

Left Anti Join in SQL

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-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So the formatting is not how I indended. The column headings are:
1

  1. Person_ID

  2. Location

  3. Date of attendance

Comment is added for info




0 Votes 0 ·

That's to less on information.

Please post table design as DDL, some sample data as DML statement and the expected result.

0 Votes 0 ·

Hi @Jason2021-1729,

Could you please provide any update?

If it is not working, please provide some sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered Jason99-3901 commented

Hi @Jason2021-1729,

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Melissa, Thanks very much for the heads-up and your solution.

I used it in my test environment (changing the row names as applicable) and got 0 rows effected. So I used the above to create and populate the table as per your code.... and then ran the select statement - again getting 0 rows returned - please see my screenshot.

To make things simpler (and reflect how it will be run) is it possible that we can define the dates explicitly?

So if we were to run the query "where DateOfAttendance between '12-Aug-2021' and '13-Aug-2021'" it should recognise that Dave is a first timer and return him in the resultset (and Bob is a reattender and therefore not return him).

Best wishes
jason





0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered Jason99-3901 commented

Hi @Jason2021-1729,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Melissa,

That works thankyou very much!

iFor completeness (and for other people's information) I should add that if I wish to add more fields in the select statement it then brings back re-attenders. I take responsibility as in my original post I said I wanted to return 'Dave' - when what I should have said is I wanted to return the 'Person_id' Dave and the associated 'Location' and the 'DateOfAttendance'.
!

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @Jason99-3901,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Superb - that is perfect.

Thank-you very much for your time and effort.

0 Votes 0 ·

Hi @Jason99-3901,

Thanks for your update and confirmation.

I noticed that you switched your account. Could you please log in with your original account and accept the answer if it helped? Your action would be helpful to other users who encounter the same issue and read this thread. 

Best regards,
Melissa

0 Votes 0 ·