CREATE VIEW EchoPathScoreBoard AS
SELECT r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcOtherIndex
    ,r.rsrcName
    ,r.userId
    ,StatsCaptureOverThresholds=100-100*SUM(case WHEN a.fieldIndex=13201 THEN (alertDuration*severity/5.0)/(24*3600) ELSE 0 END)
    ,StatsCaptureSumCompletionTime=100-100*SUM(case WHEN a.fieldIndex=13202 THEN (alertDuration*severity/5.0)/(24*3600) ELSE 0 END)
    ,StatsCollectNumDisconnects=100-100*SUM(case WHEN a.fieldIndex=13203 THEN (alertDuration*severity/5.0)/(24*3600) ELSE 0 END)
    ,StatsCollectTimeouts=100-100*SUM(case WHEN a.fieldIndex=13204 THEN (alertDuration*severity/5.0)/(24*3600) ELSE 0 END)
    ,StatsCollectBusies=100-100*SUM(case WHEN a.fieldIndex=13205 THEN (alertDuration*severity/5.0)/(24*3600) ELSE 0 END)
    ,StatsCollectNoConnections=100-100*SUM(case WHEN a.fieldIndex=13206 THEN (alertDuration*severity/5.0)/(24*3600) ELSE 0 END)
    ,StatsCollectDrops=100-100*SUM(case WHEN a.fieldIndex=13207 THEN (alertDuration*severity/5.0)/(24*3600) ELSE 0 END)
    ,StatsCollectSequenceErrors=100-100*SUM(case WHEN a.fieldIndex=13208 THEN (alertDuration*severity/5.0)/(24*3600) ELSE 0 END)
    ,StatsCollectVerifyErrors=100-100*SUM(case WHEN a.fieldIndex=13209 THEN (alertDuration*severity/5.0)/(24*3600) ELSE 0 END)
    ,availability=100-100*SUM(CASE WHEN a.fieldIndex =1602 THEN (a.alertDuration*1.0)/(24*3600) ELSE 0 END)
FROM ResourceNameEchoPathView r, AlertView a
        WHERE r.server_addr=a.server_addr
        AND r.probe_addr=a.probe_addr
        AND r.rsrcIndex=a.rsrcIndex
        AND r.rsrcOtherIndex=a.rsrcOtherIndex
    AND a.severity > 0
    AND a.severity <=5
    AND r.monitored=1
    AND a.IntervalDateTime BETWEEN dateadd(second, -datepart(second, getdate()),
        dateadd(minute, -datepart(minute, getdate()),
        dateadd(hour, -datepart(hour, getdate()),
        dateadd(day, -1, getdate()))))
    AND dateadd(second, -datepart(second, getdate()),
        dateadd(minute, -datepart(minute, getdate()),
        dateadd(hour, -datepart(hour, getdate()),
        getdate())))
    AND a.fieldIndex/100=132
GROUP BY r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcOtherIndex
    ,r.rsrcName
    ,r.userId
GO
GRANT SELECT ON  [dbo].[EchoPathScoreBoard] TO [uniadmin]
GO