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
userid
, so the first SQL query to achieve this would be
SELECT DISTINCT CONVERT( date, compoffdate, 101 ) AS date, userid
FROM tblcompoff
This query results in the following rows:
date userid
2012-12-30 21
2012-12-31 21
2012-12-30 22
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
FROM tblcompoff)
GROUP BY TOF.userid
The query performs the count over the first query we made to limit/specify our data.
The result is:
userid totaldays
21 2
22 1
I hope this make the process off how to achieve your result clear.