I have a table designed as this (simple example):
WorkRecords (UserID, DateWorked)
UserID DateWorked ----------- ----------------- 111111 '2023/09/01' 222222 '2023/09/01' 333333 '2023/09/02' 222222 '2023/09/02' 111111 '2023/09/02' 333333 '2023/09/01' 111111 '2023/09/03'
I need to query the table and return the UserIDs of every person that worked all specified dates (in real usage I am querying over a 20-day date range). In this example if I wanted to know the ID of who worked 9/1 and 9/2 and 9/3 (user 111111), what is the most efficient way to do so?
Possibly an easy way, but it eludes me. The current way I am doing it is using a CTE of each date and doing multiple UNIONs but it’s an extremely taxing query.
You can achieve this by using queries with a GROUP BY and HAVING clause to filter the results based on the specified dates. for example:
SELECT UserID FROM WorkRecords WHERE DateWorked IN ('2023-09-01', '2023-09-02', '2023-09-03') GROUP BY UserID HAVING COUNT(DISTINCT DateWorked) = 3; -- Adjust the count based on the number of dates you're checking