[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

mysql sql problem

Posted on 2011-05-12
4
Medium Priority
?
290 Views
Last Modified: 2012-06-27
I have a table that has data with an entrydate (datetime) column.
I want to be able to pull back the avg time between entries for a given ORG and Laborg combination.

So my data may look like this:

"org"      "laborg"      "entrydate"
"CNC"      "ClinixMIS"      "2011-05-11 17:56:16"
"CNC"      "ClinixMIS"      "2011-05-11 17:55:08"
"CNC"      "ClinixMIS"      "2011-05-11 17:54:37"
"CNC"      "ClinixMIS"      "2011-05-11 17:54:11"
"CNC"      "ClinixMIS"      "2011-05-11 17:53:42"
"CNC"      "ClinixMIS"      "2011-05-11 17:52:52"
"CNC"      "ClinixMIS"      "2011-05-11 17:52:04"
"CNC"      "ClinixMIS"      "2011-05-11 17:51:46"
"CNC"      "ClinixMIS"      "2011-05-11 17:51:19"
"CNC"      "ClinixMIS"      "2011-05-11 17:50:23"

So for say the last 50 entries I'd like to know the avg time between entry dates so that if I stopped getting entries for say 10 minutes I would know something is wrong.  

The end game for this is to create a monitor that polls the database every 5-10 minutes and brings back a list of orgs/laborgs that haven't had an entry within the calculated avg time that we get entries.
0
Comment
Question by:rutledgj
  • 2
4 Comments
 
LVL 4

Expert Comment

by:petr_hlucin
ID: 35746528
Try the followig select. It basically finds out for each record timestamp of the previous entry and then counts average interval between the current and the previous entry.
SELECT AVG(DATEDIFF(t.preventrydate, t.entrydate))
FROM (SELECT t2.entrydate,
(SELECT MAX(t2.entrydate)
FROM table t2
WHERE t1.org = t2.org
AND   t1.laborg = t2.laborg
AND   t1.entrydate > t2.entrydate) preventrydate
FROM table t1
WHERE t1.org = 'CNC'
AND   t1.laborg = 'ClinixMIS') t

Open in new window

0
 
LVL 21

Expert Comment

by:K V
ID: 35746533
below query will give you ORG names entered since last ten minutes:

select distinct ORG from TABLENAME where entrydate > date_sub(now(), interval 10 minute);
0
 

Author Comment

by:rutledgj
ID: 35746770
Can you make this work with the real table name?

SELECT AVG(DATEDIFF(t.preventrydate, t.entrydate))
FROM (SELECT t2.entrydate,
(SELECT MAX(t2.entrydate)
FROM  hl7trans t2
WHERE t1.org = t2.org
AND   t1.laborg = t2.laborg
AND   t1.entrydate > t2.entrydate) preventrydate
FROM  hl7trans  t1
WHERE t1.org = 'CNC'
AND   t1.laborg = 'ClinixMIS') t
LIMIT 10

This gives me errors at the table name
0
 
LVL 4

Accepted Solution

by:
petr_hlucin earned 2000 total points
ID: 35746847
Sorry, I overlooked in one reference - this one should work. If not, post here the error returned by MySQL.
SELECT AVG(DATEDIFF(t.preventrydate, t.entrydate))
FROM (
  SELECT t1.entrydate, (
    SELECT MAX(t2.entrydate)
    FROM hl7trans t2
    WHERE t1.org = t2.org
    AND   t1.laborg = t2.laborg
    AND   t1.entrydate > t2.entrydate) preventrydate
  FROM hl7trans t1
  WHERE t1.org = 'CNC'
  AND   t1.laborg = 'ClinixMIS') t

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month20 days, 10 hours left to enroll

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question