Find all times where more then 10 values per 5 minutes are registered

Hi,

I work at a factory and i'm trying to manage our alarms.
We get x alarms per minute and i would like to find the times when we get a lot of them at once.
So i would like to make a sqlquery that finds every moment that we receive more than 10 alarms every 10 minutes. How can i fix this?

Thanks

I could make a program that executes a query for every minute but i was wondering if there was an easyer way.

How do the alarms get recorded in SQL Server?

Do you want 10 minute boundaries
i.e. 00:00 - 00:10, 00:10 - 00:20...
or 10 consecutive minutes
i.e. the count between 00:00-00:10, 00:01-00:11...
or any 10 minute period
i.e take any error, count the number in the next 10 minutes, then take the next error, count....

The first is the easiest
;with cte as
(
select startdate = convert(datetime),'20180301')
union all
select dateadd(mi.10,startdate) from cte where startdate < '20180401'
)
select startdate, count()
from myerrors f
join cte t
on f.ErrorTime >= cte.startdate and f.ErrorTime < dateadd(mi,10,startdate)
group by cte.startdate
having count(
) >= 10

The second one could be very similar - just changing the cte.
The 3rd is probably simplest by just getting the starttimes, adding 10 mins (similar to the cte) then joining back to the table - but that wouldn't be very efficient.

For either of the first two I would maintain a table with the error counts already aggregated.

Your title says "5 minutes" - which is it?

Well, my suggestion is:

Query
declare @timeframe int=5;
declare @alarms int=10;

with cte(yourdatetimefield)
  as (select a.yourdatetimefield
        from yourtable as a
             inner join yourtable as b
                     on b.yourdatetimefield>=a.yourdatetimefield
                    and b.yourdatetimefield<=dateadd(minute,@timeframe,a.yourdatetimefield)
/*****
 * here you might want to limit the search period in a where clause
 * ex:
       where a.yourdatetimefield>=dateadd(day,-1,current_timestamp)
         and a.yourdatetimefield<=current_timestamp
 *
 *****/
       group by a.yourdatetimefield
       having count(*)>@alarms
     )
select distinct
       b.*       /* instead of b.*, you should specify which fields you want to see */
  from cte as a
       inner join yourtable as b
               on b.yourdatetimefield>=a.yourdatetimefield
              and b.yourdatetimefield<=dateadd(minute,@timeframe,a.yourdatetimefield)
;