question

Jason99-3901 avatar image
0 Votes"
Jason99-3901 asked Jason99-3901 commented

Left anti join query

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…


[2]: /answers/storage/attachments/125510-table.png

sql-server-generalsql-server-transact-sql
image.png (20.8 KiB)
table.png (16.3 KiB)
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.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Jason99-3901 commented

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://docs.microsoft.com/en-us/answers/questions/520422/.



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

Thanks Viorel-1. I didn't think to use 'Except'. I've tried that (albeit declaring a specific date range and that is how it will be run) and it works (ie returns just the first timers) - but with a gotcha!

The gotcha is that I need to return more in the select than just Person_ID & [Location]. Apologies for not making this clear from the start. On adding [Date of attendance]) I get reattenders returned in my query as well as first timers. Can you suggest a why whereby I can return the data of attendance (and there will be other columns) without it breaking the desired result of just returning first time attenders?

select Person_ID, [Location], Date_of_Attendance, + more fields
from Attendance
where AttendanceDate between '26-Jul-2021' and '1-Aug-2021'
except
select Person_ID, [Location], Date_of_Attendance, + more fields
from Attendance
where AttendanceDate between 'dd-mmm-yyyy' and '25-Jul-2021'
(where dd-mmm-yyyy is the earliest attendance date in the table)*


Ps: And thanks for the link to the same question I posted 4 days ago - I couldn't find it (and it was not listed as one of my threads) and I assumed it had been deleted..

0 Votes 0 ·

To display more details from the found rows, try using a join:

 select a1.Person_ID, a1.[Location], a1.[Date of attendance]
 from Attendance a1
 inner join
 (
     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)
 ) a2 on a2.Person_ID = a1.Person_ID and a2.Location = a1.Location


0 Votes 0 ·

Hi Viorel-1 - that works!

Thanks alot for your time and effort - that is so useful and it'll be a technique I can add to the memory banks to use again.

Best wishes
Jason

0 Votes 0 ·