Re: Looking for elegant solution to sensor / reading / alert database design.

On Dec 9, 12:46 pm, "Mark S. (UK)" <ma..._at_someaccount.net> wrote:
> The upper and lower limit is the limits to which the measured element
> must stay between - and if it goes outside of these limits (for a
> configured length of time) an alert is generated.

Based on the suggestions in my previous post, you could use a view like the following to find the length of time sensors are out of bounds.

CREATE VIEW SensorLimitsExceeded AS
SELECT a.Sensor, a.Time, a.Reading, a.Time - b.Time AS AlertDuration   FROM SensorReadings cur, SensorReadings prv, SensorLimits lim

 WHERE cur.Sensor = prv.Sensor
   AND cur.Reading NOT BETWEEN lim.LowerLimit AND lim.UpperLimit
   AND prv.Time = (SELECT Max(hst.Time) FROM SensorReadings hst,

 WHERE hst.Sensor = cur.Sensor AND hst.Sensor = lmh.Sensor
   AND hst.Time < cur.Time
   AND hst.Reading BETWEEN lmh.LowerLimit AND lmh.UpperLimit)