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)
;