Returning an unique record for multiple users

I’m trying to get time between 2 dates. I’ve got a table with different holidays, DateTime and userId. There is several different holidays but what counts for me are Christmas and Easter. I would like to get time from the oldest logged holiday to the newest for every single user. (doesn’t matter Christmas or Easter it was)

Now I get only get result for one user. I tried also WHERE holiday LIKE ‘Christmas’ OR/AND ‘EASTER’ but doesn’t work properly.

WITH XYZ as(
SELECT userID,
max(dt) as the_oldest,
min(dt) as the_newest, 
datediff(max(dt), min(dt))
FROM data
WHERE holiday NOT LIKE 'Halloween') 
SELECT * FROM XYZ;

Thanks in advance!

Answer

You have to GROUP BY userID so that query will return max and min date for each user.

SELECT userID,
max(dt) as the_newest,
min(dt) as the_oldest,
datediff(max(dt), min(dt))
FROM data
WHERE holiday NOT LIKE 'Halloween'
GROUP BY userID

Please note max(dt) will give newest date and min(dt) oldest. It was opposite in your query.

Advertisement

Answer