--
-- PerformanceDBA for Mark/Sensor 110113
--
--     Register Alert from Reading
--
-- This will be executed within the Monitor Time (30 secs ?) loop (shown); one 
-- INSERT-SELECT per AlertType & Value/Switch is reqd, which can be processed
-- in an inner loop (one is shown).
--     @PrevDateTime  = DateTime of the *previous* iteration of the loop
--     @StartDateTime = program start DateTime (to constrain the scan range)
--     @AlertTypeCode = current AlertType being processed by the inner loop
--
-- Syntax & operation (ReadingValue & Alert only) tested on Sybase ASE 15.0.3
--

DECLARE @Variables ...
SET @StarDateTime = GETDATE()                       -- Program start

WHILE (1=1)                                         -- forever
    BEGIN
    SET @DateTime = GETDATE()                       -- for this iter; actual

    -- Main Code Block
    --     AlertType x INSERT-SELECT
    --     OR use a vector on AlertTypeCode, and Project (2 x INSERT-SELECT)

    INSERT Alert                                    -- one example INSERT-SELECT
        -- IFF there is a fresh out-of-range Reading ...
        SELECT R.LocationId,
               R.SensorNo,
               R.ReadingDtm,
               @AlertTypeCode                       -- AlertType [H]igh [L]ow 
            FROM Sensor       S,
                 ReadingValue R
            WHERE S.LocationId = R.LocationId
            AND   S.SensorNo   = R.SensorNo
            AND   S.IsSwitch   = 0                  -- Value types only
            AND   R.ReadingDtm > @PrevDateTime      -- get new Readings for this iteration
            AND   R.Value      > S.UpperLimit       -- out-of-range
    
            -- ... with no corresponding Alert
            AND   NOT EXISTS ( SELECT 1             -- never *
                FROM  Alert
                WHERE LocationId  = R.LocationId
                AND   SensorNo    = R.SensorNo
                AND   AlertType   = @AlertTypeCode  -- for this iter
    
                -- ie. later than the latest *within-range* Reading
                AND   ReadingDtm  > ( SELECT MAX(ReadingDtm)
                    FROM  ReadingValue 
                    WHERE LocationId  = R.LocationId 
                    AND   SensorNo    = R.SensorNo
                    AND   Value      <= S.UpperLimit
                    -- performance: constrain range to reasonable
                    AND   ReadingDtm  > @StartDateTime
                    )
                )

    SET @PrevDateTime = @DateTime                   -- catch actual, not intended
    -- WAITFOR DELAY "00:00:30"                     -- disabled
    END


-- ReadingValue Test Data.  Sensor.UpperLimit is 50
-- One iteration of the above was tested for each ReadingDtm minus 00:01 below

INSERT ReadingValue VALUES (1, 1, "2011.01.01 00:01", 20)--
INSERT ReadingValue VALUES (1, 1, "2011.01.01 00:02", 52)--
INSERT ReadingValue VALUES (1, 1, "2011.01.01 00:03", 55)--
INSERT ReadingValue VALUES (1, 1, "2011.01.01 00:04", 51)--
INSERT ReadingValue VALUES (1, 1, "2011.01.01 00:05", 20)--
INSERT ReadingValue VALUES (1, 1, "2011.01.01 00:06", 60)--

-- SELECT * FROM ReadingValue 

LocationId,SensorNo,ReadingDtm,Value
1,1,2011-01-01 00:01:00.0,20
1,1,2011-01-01 00:02:00.0,52
1,1,2011-01-01 00:03:00.0,55
1,1,2011-01-01 00:04:00.0,51
1,1,2011-01-01 00:05:00.0,20
1,1,2011-01-01 00:06:00.0,60

-- SELECT * FROM Alert 

LocationId,SensorNo,ReadingDtm,AlertTypeCode
1,1,2011-01-01 00:02:00.0,"H"
1,1,2011-01-01 00:06:00.0,"H"

