CREATE VIEW RMONScoreBoard AS
SELECT r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcOtherIndex
    ,r.rsrcName
    ,r.userId
    ,etherStatsOctets=100-case WHEN SUM(etherStatsOctets) > 100 THEN 100 ELSE SUM(etherStatsOctets) END
    ,etherStatsBroadcastPkts=100-case WHEN SUM(etherStatsBroadcastPkts) > 100 THEN 100 ELSE SUM(etherStatsBroadcastPkts) END
    ,etherStatsMulticastPkts=100-case WHEN SUM(etherStatsMulticastPkts) > 100 THEN 100 ELSE SUM(etherStatsMulticastPkts) END
    ,etherStatsCRCAlignErrors=100-case WHEN SUM(etherStatsCRCAlignErrors) > 100 THEN 100 ELSE SUM(etherStatsCRCAlignErrors) END
    ,etherStatsCollisions=100-case WHEN SUM(etherStatsCollisions) > 100 THEN 100 ELSE SUM(etherStatsCollisions) END
    ,etherStatsDropEvents=100-case WHEN SUM(etherStatsDropEvents) > 100 THEN 100 ELSE SUM(etherStatsDropEvents) END
    ,etherStatsFragments=100-case WHEN SUM(etherStatsFragments) > 100 THEN 100 ELSE SUM(etherStatsFragments) END
    ,availability=100-case WHEN SUM(unavail) > 100 THEN 100 ELSE SUM(unavail) END
FROM RMONAlertView r
GROUP BY
    r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcOtherIndex
    ,r.rsrcName
    ,r.userId
GO
GRANT SELECT ON  [dbo].[RMONScoreBoard] TO [uniadmin]
GO