In MSSQL I'm trying to return the number of rows where the count of a certain activity exceeds X and has occurred more then y number of times in a month.
This query tells me how many times on a given day for the current month the activity has occurred.
SELECT COUNT(DATEPART(DAY,myDATETIME))as dayCount,DATEPART(DAY,myDATETIME)as theDAY, Activity FROM ALL_Activities WHERE DATEPART(YEAR,myDATETIME)= DATEPART(YEAR,GETDATE()) AND DATEPART(MONTH,myDATETIME)= DATEPART(MONTH,GETDATE()) AND Activity = 'The door opened' GROUP BY (DATEPART(DAY,myDATETIME)), Activity HAVING COUNT(DATEPART(DAY,myDATETIME)) > 0 AND ???????
dayCount, theDAY, Activity 3 19 The door opened 7 18 The door opened 7 17 The door opened 1 15 The door opened 2 14 The door opened 1 13 The door opened 5 12 The door opened 1 11 The door opened 10 10 The door opened 4 9 The door opened 1 8 The door opened 2 7 The door opened
What I'm trying to end up with is a query that returns the activity when it has happened at least X times a day more then Y times a month such that A single row matching the criteria returned would have the total number of occurrences, the total number of days, and the activity. Is it possible to do this strictly within SQL?
44, 12, The door opened
If not that, then even the rows that meet the criteria would help. Meaning maybe return the rows when the number of days in the given month is >=5 where the times per day >=1. I tried using some combination of sub queries, @@ROWCOUNT, and other things, but couldn't get anything to work.