CREATE VIEW AlertView AS
SELECT server_addr,probe_addr, rsrcIndex,
    rsrcOtherIndex = CASE rsrcType
    WHEN 1 THEN 0
    WHEN 3 THEN rsrcIndex
    ELSE rsrcOtherIndex
    END,
    rsrcType,a.fieldIndex,fieldDescShort,unit
    ,value=t.displayScaleFactor*a.value
    ,threshold=t.displayScaleFactor*a.threshold
    ,severity,
    thresholdType=CASE WHEN value > threshold THEN 5
        WHEN value < threshold AND severity = 0 THEN 5 ELSE 6 END,
    IntervalDateTime, IntervalDuration
    ,endTime=CASE WHEN a.fieldIndex/100=16 THEN dateadd(second, IntervalDuration,IntervalDateTime)
        ELSE endTime END
    ,alertDuration=CASE WHEN a.fieldIndex/100=16 THEN IntervalDuration
        ELSE datediff(second, IntervalDateTime, endTime) END
    FROM network_Alert a, ThresholdDesc t
    WHERE a.fieldIndex=t.fieldIndex
GO
GRANT SELECT ON  [dbo].[AlertView] TO [uniadmin]
GO