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

On Dec 10, 1:57 am, Nilone <rea..._at_gmail.com> wrote:
> 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,
> SensorLimits lmh
>  WHERE hst.Sensor = cur.Sensor AND hst.Sensor = lmh.Sensor
>    AND hst.Time < cur.Time
>    AND hst.Reading BETWEEN lmh.LowerLimit AND lmh.UpperLimit)

Oops, replace a and b with cur and prv, respectively. Received on Fri Dec 10 2010 - 01:00:32 CET