--
-- PerformanceDBA for Mark/Sensor 110120
--
--     Basic Requirement: Report Alerts with Peak Value
--
--     Syntax & operation tested on Sybase ASE 15.0.3
--

SELECT  [Location]   = Location.Name,
        [Sensor]     = AO.SensorNo,
        [DateTime]   = AO.ReadingDtm,
        [Type]       = AlertType.Name,

        -- AlertValue (1) select Reading that generated the Alert
        [AlertValue] = (SELECT Value
            FROM  ReadingValue                     -- Inner query, no alias reqd
            WHERE LocationId  = AO.LocationId      -- Correlate with Outer
            AND   SensorNo    = AO.SensorNo        -- straight join
            AND   ReadingDtm  = AO.ReadingDtm
            ),

        -- PeakValue (3) select max value within Alert duration
        [PeakValue]  = ( SELECT MAX(Value)
            FROM  ReadingValue
            WHERE LocationId  = AO.LocationId
            AND   SensorNo    = AO.SensorNo
            AND   ReadingDtm >= AO.ReadingDtm      -- Start range DTM

            -- End range DTM (2) select next DTM where *Value* is within-range
            AND   ReadingDtm < ( SELECT ISNULL( MIN(ReadingDtm), AO.ReadingDtm)
                FROM  ReadingValue 
                WHERE LocationId  = AO.LocationId
                AND   SensorNo    = AO.SensorNo
                AND   ReadingDtm >  AO.ReadingDtm   -- start range DTM
                AND   Value      <= SO.UpperLimit   -- End range Value
                )
            )
    FROM  Alert      AO,                            -- Alert in Outer query
          AlertType,
          Location,
          Sensor     SO                             -- Sensor in Outer query 
    WHERE AO.AlertTypeCode = AlertType.AlertTypeCode
    AND   AO.LocationId    = Location.LocationId
    AND   AO.LocationId    = SO.LocationId
    AND   AO.SensorNo      = SO.SensorNo
    AND   SO.IsSwitch      = 0                      -- Value types only
    AND   AO.ReadingDtm   >= "2011.01.01 00:00"     -- Report start DTM

    -- exclude Acknowledged (4) select Acknowledged rows
    AND   NOT EXISTS (SELECT 1
        FROM  Acknowledgement
        WHERE LocationId  = AO.LocationId
        AND   SensorNo    = AO.SensorNo
        AND   ReadingDtm  = AO.ReadingDtm
        )

    -- exclude Actioned (5) select Actioned rows
    AND   NOT EXISTS (SELECT 1
        FROM  Action
        WHERE LocationId  = AO.LocationId
        AND   SensorNo    = AO.SensorNo
        AND   ReadingDtm  = AO.ReadingDtm
        )
