I am trying to find the count of days listed by person where they have over 100 records in the recordings table. It is having a problem with the having clause, but I am not sure how else to distinguish the counts by person. There is also a problem with the where clause, I also tried putting "where Count(Recordings.ID) > 100" and that did not work either. Here is what I have so far:
SELECT Person.FirstName,
Person.LastName,
Count(Recordings.ID) AS DAYS_ABOVE_100
FROM Recordings
JOIN Person ON Recordings.PersonID=Person.ID
WHERE DAYS_ABOVE_100 > 100
AND Created BETWEEN '2013-08-01 00:00:00.000' AND '2013-08-21 00:00:00.000'
GROUP BY Person.FirstName,
Person.LastName
HAVING Count(DISTINCT PersonID), Count(Distinct Datepart(day, created))
ORDER BY DAYS_ABOVE_100 DESC
Example data of what I want to get:
First Last Days_Above_100
John Doe 5
Jim Smith 12
This means that for 5 of the days in the given time frame, John Doe had over 100 records each day.
For the sake a readability, I would break the problem into two parts.
First, figure out how many recordings each person has for a day. This is the query in the common table expression (the first select statement). Then select against the common table expression to limit the rows to only those that you need.