Aggregate an Aggregate 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)) &gt; 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.
Using  with common_table_expression() and count() over(): we can get a running total for the count.

query:

with cte as (
  select distinct
      TheDate= convert(varchar(10),convert(date,mydatetime),120)
    , TheDay = datepart(day,mydatetime)
    , DailyActivityCount = count(*) over (
          partition by convert(date,mydatetime), activity
          )
    , runningActivityCount = count(*) over (
          partition by activity 
          order by convert(date,mydatetime)
          )
    , Activity
  from all_activities
  where activity = 'The door opened'
    and mydatetime &gt;= dateadd(month, datediff(month, 0, getdate()), 0)
    and mydatetime &lt; dateadd(month, datediff(month, 0, getdate())+1, 0)
  )
select 
    *
  from cte
  --where runningActivityCount &gt; 12
  order by TheDay


rextester results: http://rextester.com/WOW38735

+------------+--------+--------------------+----------------------+-----------------+
|  TheDate   | TheDay | DailyActivityCount | runningActivityCount |    Activity     |
+------------+--------+--------------------+----------------------+-----------------+
| 2017-01-07 |      7 |                  2 |                    2 | The door opened |
| 2017-01-08 |      8 |                  1 |                    3 | The door opened |
| 2017-01-09 |      9 |                  4 |                    7 | The door opened |
| 2017-01-10 |     10 |                 10 |                   17 | The door opened |
| 2017-01-11 |     11 |                  1 |                   18 | The door opened |
| 2017-01-12 |     12 |                  5 |                   23 | The door opened |
| 2017-01-13 |     13 |                  1 |                   24 | The door opened |
| 2017-01-14 |     14 |                  2 |                   26 | The door opened |
| 2017-01-15 |     15 |                  1 |                   27 | The door opened |
| 2017-01-17 |     17 |                  7 |                   34 | The door opened |
| 2017-01-18 |     18 |                  7 |                   41 | The door opened |
| 2017-01-19 |     19 |                  3 |                   44 | The door opened |
+------------+--------+--------------------+----------------------+-----------------+


update based on question in comment: 

Declare @Activity varchar(64) = 'The door opened';
Declare @DailyThreshold int = 2;
Declare @Month varchar(7) =  '2017-01'; 
Declare @NthDay int = 5; -- for more then 5 days in a month(Y)


;with countCte as (
  select distinct
      TheMonth= convert(varchar(7),convert(date,mydatetime),120)
    , TheDate= convert(varchar(10),convert(date,mydatetime),120)
    , TheDay = datepart(day,mydatetime)
    , Activity
    , DailyActivityCount = count(*) over (
        partition by convert(date,mydatetime), activity
          )
    , runningActivityCount = count(*) over (
        partition by convert(varchar(7),convert(date,mydatetime),120), activity 
        order by convert(date,mydatetime)
          )
    , MonthlyActivityCount = count(*) over (
        partition by convert(varchar(7),convert(date,mydatetime),120), activity
          )

  from all_activities
  where activity = @Activity
    and mydatetime &gt;= dateadd(month, datediff(month, 0, getdate()), 0)
    and mydatetime &lt; dateadd(month, datediff(month, 0, getdate())+1, 0)
  )
, cte as (
    select *
      , rn = row_number() over (
          partition by Activity 
          order by TheDate
          )
      --, 
      from countCte
      where 1=1
        and DailyActivityCount &gt; @DailyThreshold
        and TheMonth = @Month
)
select 
    top 1
    *
  from cte
  where rn &gt; @NthDay
  order by TheDay