Sql-server – select users who do something on each date in sql server

sql server

I have a table with 3 columns – user_id, building_id, datetime where each record is an access to a specific building and hence users can accesss a building many times. I need to select each user_id from each day who has accessed a specific building. It would seem like I need to cast the datetime to just a day and do a distinct across user_id, building_id and day. How would I do this in a modern version of SQL Server?

Best Answer

The query to list the users and dates for a given building (defined by @BuildingID):

DECLARE @BuildingID int = 42;

SELECT 
    user_id,
    [Date] = CONVERT(date, [datetime])
FROM dbo.tablename
WHERE building_id = @BuildingID
GROUP BY 
    user_id, 
    CONVERT(date, [datetime])
ORDER BY
    [Date], user_id;

Beyond that, the requirement is not clear. In the title you say "on each date" - if you have a set of dates and you want the users who accessed @BuildingID on all of those dates, one approach could be:

DECLARE @BuildingID int = 42;

DECLARE @dateCount int;
DECLARE @dates TABLE([Date] date UNIQUE);

INSERT @Dates([Date]) VALUES('20190606'),('20190609');
SELECT @dateCount = COUNT(*) FROM @Dates;

;WITH users AS
(
  SELECT 
    t.user_id
  FROM dbo.tablename AS t
  INNER JOIN @Dates AS d
     ON t.[datetime] >= d.[Date]
    AND t.[datetime] < DATEADD(DAY, 1, d.[Date])
  WHERE t.building_id = @BuildingID
  GROUP BY 
    t.user_id
)
SELECT user_id 
  FROM users
  GROUP BY user_id
  HAVING COUNT(*) = @dateCount;

Not sure if you are querying the data more often than you are writing it, but this might be a use case for an indexed view.

Related Question