...

View Full Version : Mysql 3 record in 1 hr = 1 record in another table



rolperez
01-17-2012, 04:33 AM
Hi!

I have a problem, i have a table, where the information of some devices are deposited if they have an alert, the structure of the table is this:

ID | SERVERID | TEMP | SENSOR | PROCESSED | DATETIME
1 | 12458788 | 23.3 | 1 | 0 |2012-01-16 16:50:01

I need to send the information to another table, if i have 3 records in 1 hour, the script sends only 1 record to another table or 2 followed records = 1 record in another table , do you have some ideas? i really really need help!

Thank for your time!!:thumbsup:

[EDIT]

The table populates with various SERVERID's in an hour.....i need to send 1 record to another table for every 3 records in an hour of every serverid..... Thanks!!

BluePanther
01-17-2012, 07:34 AM
What would this 1 record be?

Also, why would you want to do this? Why not just pull the data from the other table (I'm assuming both tables are in the same database) as you need it?

rolperez
01-17-2012, 08:34 AM
Hi! Ihave 2 tables 'prealarms' and 'alarms'


the structure of the table 'prealarms' are this:
ID | SERVERID | TEMP | SENSOR | PROCESSED | DATETIME
1 | 12458788 | 23.3 | 1 | 0 |2012-01-16 16:50:01
2 | 12458789 | 24.3 | 1 | 0 |2012-01-16 16:55:01
3 | 12458786 | 21.3 | 1 | 0 |2012-01-16 16:55:01
4 | 12458788 | 21.3 | 1 | 0 |2012-01-16 16:55:01
etc..

and i need a query to separate the records by id and if there are three records in a lapse of one hour, send the 'serverid' to the 'alarms' table....only 1 record every 3 records found in a lapse of one hour...

Thanks!

themousemaster
01-17-2012, 08:46 PM
Is what you are trying to accomplish

--- for every hour, if it has 3 or more records, I need to make a record in the other table

or is it

--- for every hour, I need to write (#records / 3 rounded down) records to the other table

or is it

--- if there exists any record for which 2 other records are within 60 minutes of it, write a record to the other table

?



(The best solution will differ depending on which it is)

rolperez
01-17-2012, 08:53 PM
One server send 1 record every 5 minutes, then the maximum number of records for 1 servers in 'prealarms' are 12....every 3 records i need to send 1 record to 'alarms'....

the best are this i think:


"--- for evert hour, if it has more than 3 records, I need to make a record in the other table"

Thanks!

rolperez
01-17-2012, 10:40 PM
any suggestions?

rolperez
01-18-2012, 12:42 AM
I thik i find an answer, if you have something better, please reply.

The code i was find is this:



SELECT
prealarmas.preid,
prealarmas.serverid,
prealarmas.temp,
prealarmas.visto,
prealarmas.datetime,
HOUR(prealarmas.datetime) AS h, COUNT(*)
FROM
prealarmas
WHERE
prealarmas.serverid = '12345678887' AND
prealarmas.visto = '0'
GROUP BY
h


And if h is = or > 3 , insert bla, bla... into 'alarms'

What doy you think?

themousemaster
01-18-2012, 01:38 PM
that would be similar to what I would have suggested.

You are grouping on "h" though, so you dont want to look for h >= 3, you want to look for COUNT(*) >= 3



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum