CREATE VIEW IPScoreBoard AS
SELECT r.server_addr
    ,r.probe_addr
    ,r.rsrcName
    ,r.userId
    ,ipInReceives=100-CASE WHEN SUM(ipInReceives)>100 THEN 100 ELSE SUM(ipInReceives) END
    ,ipInErrors=100-CASE WHEN SUM(ipInErrors)>100 THEN 100 ELSE SUM(ipInErrors) END
    ,ipForwDatagrams=100-CASE WHEN SUM(ipForwDatagrams)>100 THEN 100 ELSE SUM(ipForwDatagrams) END
    ,ipInDelivers=100-CASE WHEN SUM(ipInDelivers)>100 THEN 100 ELSE SUM(ipInDelivers) END
    ,ipOutErrors=100-CASE WHEN SUM(ipOutErrors)>100 THEN 100 ELSE SUM(ipOutErrors) END
    ,ipOutRequest=100-CASE WHEN SUM(ipOutRequest)>100 THEN 100 ELSE SUM(ipOutRequest) END
    ,availability=100-CASE WHEN SUM(unavail)>100 THEN 100 ELSE SUM(unavail) END
FROM IPAlertView r
GROUP BY
    r.server_addr
    ,r.probe_addr
    ,r.rsrcName
    ,r.userId
GO
GRANT SELECT ON  [dbo].[IPScoreBoard] TO [uniadmin]
GO