1

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.

2 Answers 2

0

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]
8
  • Thank you. I think this is really close to what I am looking for in that it presents a data grid showing the data and identifying how many days an activity has exceeded a daily count threshold by month. I'll see if I can refine it further to return only those activities in the current month that exceed the daily count threshold and the number of days in a month threshold.
    – NewtoCloud
    Jan 30, 2017 at 18:51
  • if there were a way to add HAVING activity_days > Y this might be it.
    – NewtoCloud
    Feb 1, 2017 at 21:03
  • activity_days per months? see updated answer Feb 1, 2017 at 21:07
  • Or do you need it for activity_days_over_threshold? Feb 1, 2017 at 21:08
  • No I think you have it. I made the same edit right after I made the comment. Testing again.
    – NewtoCloud
    Feb 1, 2017 at 21:12
1

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
4
  • I'm having some syntax issues try to get this work. Line 8 "order by is looking for a ")". If I place that at the end of line 7 after partition by actiivity it seems to resolve that. Line 11 however i looking for "(" or "AS"
    – NewtoCloud
    Jan 30, 2017 at 17:52
  • ok, I guess MSSMS see syntax errors that do not exist. This too is very helpful. I need to prune this down to one row predicated on a daily occurrence threshold of X and a number of days it occurred threshold of Y for the current month. If activity a occurred at least twice a day(X) for more then 5 days in a month(Y), show me the row including the total number of occurrences and number of days for the month
    – NewtoCloud
    Jan 30, 2017 at 20:52
  • @NewtoCloud Well since it isn't January anymore, there isn't test data for "the current month" anymore. Updated the rextester to use January instead of getting the current month.
    – SqlZim
    Feb 1, 2017 at 20:46
  • With DailyThreshold set to 1 and NthDay set to 5, I'm getting one row returned telling me on the 17th there were 7 occurrences where the door opened with a cumulative total of 34 to that point and a total of 44 for the month and rn=6. While its true, 7 occurrences on the 17th, cumulative is 34 and the month total is 44, its not really what I'm interested in. With the settings mentioned, I expect the row to only show if there are more then 5 days where the door opened at least once showing 12 (number of days this happened more then once), and the 44, the total number of times it happened
    – NewtoCloud
    Feb 1, 2017 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.