CREATE VIEW InterfaceScoreBoard AS
SELECT r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcName
    ,r.userId
    ,ifInOctets=100-CASE WHEN SUM(ifInOctets)>100 THEN 100 ELSE SUM(ifInOctets) END
    ,ifOutOctets=100-CASE WHEN SUM(ifOutOctets)>100 THEN 100 ELSE SUM(ifOutOctets) END
    ,inboundErrors=100-CASE WHEN SUM(inboundErrors)>100 THEN 100 ELSE SUM(inboundErrors) END
    ,outboundErrors=100-CASE WHEN SUM(outboundErrors)>100 THEN 100 ELSE SUM(outboundErrors) END
    ,availability=100-CASE WHEN SUM(unavail)>100 THEN 100 ELSE SUM(unavail) END
FROM InterfaceAlertView r
GROUP BY
    r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcName
    ,r.userId
GO
GRANT SELECT ON  [dbo].[InterfaceScoreBoard] TO [uniadmin]
GO