Aggregate an Aggregate

Question

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.

Answer 1

This report will give you all the information.
Use it as a base and filter it however you want.

declare @day_activities_threshold int = 10 

select      activity
           ,dateadd(month,datediff(month,0,[day]),0)                                            as [month]
           ,count(*)                                                                            as activity_days
           ,count(case when day_activities >= @day_activities_threshold then 1 end)             as activity_days_over_threshold
           ,sum(day_activities)                                                                 as month_activities
           ,sum(case when day_activities >= @day_activities_threshold then day_activities end)  as month_activities_over_threshold

from       (select      activity 
                       ,cast (mydatetime as date)   as [day]
                       ,count(*)                    as day_activities

            from        all_activities

            group by    activity
                       ,cast (mydatetime as date)
            ) t

group by    activity
           ,dateadd(month,datediff(month,0,[day]),0)

having      count(*) > ...  

order by    activity
           ,[month]

Answer 2

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 >= dateadd(month, datediff(month, 0, getdate()), 0)
    and mydatetime < dateadd(month, datediff(month, 0, getdate())+1, 0)
  )
select 
    *
  from cte
  --where runningActivityCount > 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 >= dateadd(month, datediff(month, 0, getdate()), 0)
    and mydatetime < 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 > @DailyThreshold
        and TheMonth = @Month
)
select 
    top 1
    *
  from cte
  where rn > @NthDay
  order by TheDay