I have a simple SQL log table (named market_history in SQLite) for US markets it looks something like this:
Sample table (market_history)
id datetime market percent
1 9/5/2014 7:50 ARIZONA 50.0
2 9/5/2014 7:50 ATLANTA 97.4
3 9/5/2014 7:50 AUSTIN 78.8
4 9/5/2014 7:50 BOSTON 90.9
6 9/5/2014 7:50 CHARLOTTE 100.0
7 9/5/2014 7:50 CHICAGO 90.3
This table is an hourly snapshot of network capacity in various systems in each market. What I would like to do is set up an alert system that if any one particular market is below a threshold percent (say 50) for more than 2 consecutive hours (each row is recorded every hour), it triggers an alert email.. So the query should show me a a unique list of Market names where the percents is < 50.0 for more than the last 2 consecutive entries
Here's the SQL I'm trying, but it's not working:
Sample SQL (not working):
SELECT
mh.datetime, mh.market, mh.percent
FROM markets_history mh
WHERE
(SELECT mh1.precent FROM markets_history mh1 WHERE mh1.datetime BETWEEN "2015-03-23 00:00:00" AND "2015-03-23 00:59:59" AND mh.market=mh1.market ) < 50 AND (SELECT mh2.precent FROM markets_history mh2 WHERE mh2.datetime BETWEEN "2015-03-23 01:00:00" AND "2015-03-23 01:59:59" AND mh.market=mh2.market ) < 50
ORDER by mh.datetime
I know I'm missing something.. any sugggestions
Aucun commentaire:
Enregistrer un commentaire