asked on

mysql sql problem

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.

petr_hlucin

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

theGhost_k8

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

rutledgj

ASKER

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

ASKER CERTIFIED SOLUTION

petr_hlucin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial