MS SQL Query to find all User IDs that worked all specified dates

October 2, 2023

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.

>Solution :

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