I need to get sum of days. The sum should be a number.
For summing should be taken only unique dates.
In below table there are 3 records for userid 1.
I need to get result equal 2 because he worked 2 days, 04.06.2018 and 05.06.2018.
UserID Date
1 04.06.2018 10:45:11
1 04.06.2018 17:15:11
1 05.06.2018 09:15:33
Desired result is:
UserID TotalDays
1 2
How to achive that? Thanks!
If you want to get TotalDays per user:
SELECT UserId, COUNT(DISTINCT DATEDIFF(DAY, 0, Date)) TotalDays
FROM UserDate
GROUP BY UserID
I did it myself.
-- Bad query
SELECT count(distinct CAST(Date as int))
-- Slightly better
SELECT count(distinct cast(cast(Date as float) as int))
Note: It's not recommended to cast straight to an int, as MSSQL rounds the value up if you're past mid day!
CAST(Date as int) can return the same value for 2 dates with different days
SELECT Date, CAST(Date as int) FROM table. In your example, 2nd and 3rd dates have different days, but the query will return 43254 for both.