--
-- PerformanceDBA for Mark/Sensor 110114
--
--     Load Alert test data from Reading test data
--
-- Existing Register Alert code, Modified loop to load test data
-- Refer to unmodified version
--
-- Untested.
--

DECLARE @Variables ...
SET @PrevDateTime = <StartOfTestData>               -- Test data start

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

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

    INSERT Alert
        -- IFF there is a fresh out-of-range Reading for this iter ...
        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.ReadinDtm <= @PrevDateTime + "00:00:30"  -- end of this pretend iter

            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 = @PrevDateTime + "00:00:30"  -- pretend iter
    -- WAITFOR DELAY "00:00:30"                     -- disabled
    END
