CREATE VIEW FRScoreBoard AS
SELECT r.server_addr
    ,r.probe_addr
    ,r.rsrcName
    ,r.userId
    ,r.rsrcIndex
    ,r.rsrcOtherIndex
    ,frCircuitSentOctets=100-CASE WHEN SUM(frCircuitSentOctets) > 100 THEN 100 ELSE SUM(frCircuitSentOctets) END
    ,frCircuitSentFrames=100-CASE WHEN SUM(frCircuitSentFrames) > 100 THEN 100 ELSE SUM(frCircuitSentFrames) END
    ,frCircuitReceivedFECNs=100-CASE WHEN SUM(frCircuitReceivedFECNs) > 100 THEN 100 ELSE SUM(frCircuitReceivedFECNs) END
    ,frCircuitReceivedBECNs=100-CASE WHEN SUM(frCircuitReceivedBECNs) > 100 THEN 100 ELSE SUM(frCircuitReceivedBECNs) END
    ,frCircuitReceivedFrames=100-CASE WHEN SUM(frCircuitReceivedFrames) > 100 THEN 100 ELSE SUM(frCircuitReceivedFrames) END
    ,frCircuitReceivedOctets=100-CASE WHEN SUM(frCircuitReceivedOctets) > 100 THEN 100 ELSE SUM(frCircuitReceivedOctets) END
    ,availability=100-CASE WHEN SUM(unavail) > 100 THEN 100 ELSE SUM(unavail) END
FROM FRAlertView r
GROUP BY
    r.server_addr
    ,r.probe_addr
    ,r.rsrcName
    ,r.userId
    ,r.rsrcIndex
    ,r.rsrcOtherIndex
GO
GRANT SELECT ON  [dbo].[FRScoreBoard] TO [uniadmin]
GO