|
To: midrange-l@xxxxxxxxxxxx
Subject: RE: SQL: How to create a list of MISSING numbers in a sequence
Date: Mon, 29 Dec 2014 19:27:45 +0000
Thanks for the several ideas suggesting to create a "numbers" table.
That will actually work perfectly for the requirements that I presented.
However, I have a feeling that sometimes a 1 minute lack of activity will not be adequate to identify a freeze, for example at 6 am prior to full business activity.
So I would like to modify the requirements to show any 2 minute or even 5 minute gaps? (Hey users modify requirements all the time, why shouldn't I haha).
Im not sure a table of numbers will identify that larger gap.
I was thinking maybe some type of MIN/MAX statement to find the max time between two adjacent times??
Any other creative or clever ideas to do this?
I appreciate the trigger idea, however on our production box that would take quite an effort to get mgmt. approval on such a change.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, December 29, 2014 12:02 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: How to create a list of MISSING numbers in a sequence
Joel,
The easiest way to handle gap finding is to have a "numbers table" from 0
to 1 million or whatever. You can use it for all kinds of cool things...
https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable
In your specific case, you might consider a specialized "hour minute" table
(or even a view over the numbers table) that just goes from 00:00 to 23:59.
Charles
On Mon, Dec 29, 2014 at 12:45 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:
We are a heavy Webdocs (document management s/w) user, and webdocs is--
intermittently freezing for 20-30 minutes a few times per week. It
self-recovers with no intervention.
I would like to send an email to several IT personnel when this occurs,
instead of having users call us.
So, how to detect when Webdocs is unresponsive?
There is a log file of every event that Webdocs records.
I would like to query the log file, and identify gaps in time that no
activity has occurred. I was thinking to run a job every 5 minutes or
so, and if there are any minute gaps in the log file (during the business
day), then I would know that a freeze has probably occurred.
Is it possible in SQL to inform me when a "minute" record doesn't exist in
a sequence?
For example, if there were events at 9:36, 9:37, 9:38, 9:39, and 9:41, and
it is now 9:41, can SQL somehow inform me that 9:40 is missing?
Thanks!
Here is the SQL that I have so far:
select
hour(ldate),
minute(ldate),count(*)
from rjsimage/doclog00
where minute(timestamp(curdate(),curtime())- ldate) <20
and date(curdate()) = date(ldate)
and hour(curtime()) = hour(ldate)
group by hour (ldate),
minute(ldate)
order by minute(ldate)
....+....1....+....2....+....3....+....4....+....
HOUR MINUTE COUNT ( * )
9 36 10
9 37 20
9 38 16
9 39 23
9 40 19
9 41 15
9 42 18
9 43 21
9 44 7
9 45 16
9 46 6
9 47 9
9 48 16
9 49 11
9 50 27
9 51 10
9 52 14
9 53 15
9 54 14
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs SkyScan
service.
________________________________________________________________________
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.