First you need to make sure you have a dataset on which you want to count the rows.
In your case you only want to count the unique days for an
, so the first SQL query to achieve this would be
SELECT DISTINCT CONVERT( date, compoffdate, 101 ) AS date, userid
This query results in the following rows:
Now based on this data set we can count the number off days for each user id by using the following query:
SELECT TOF.userid, COUNT( TOF.date ) AS totaldays
FROM (SELECT DISTINCT CONVERT( date, compoffdate, 101 ) AS date, userid
GROUP BY TOF.userid
The query performs the count over the first query we made to limit/specify our data.
The result is:
I hope this make the process off how to achieve your result clear.