CREATE view  M2ifStatsThView AS SELECT M2ifStats.server_addr, M2ifStats.probe_addr, M2ifStats.ifIndex, IntervalDateTime, IntervalDuration, ifInOctets=case WHEN (ifSpeed*IntervalDuration)= 0 THEN 0 WHEN ifSpeed = 1 THEN 0 ELSE ifInOctets*8.0/(ifSpeed*IntervalDuration) END, ifInDiscards=case WHEN (ifInUcastPkts+ifInNUcastPkts)= 0 THEN 0 ELSE ifInDiscards/(ifInUcastPkts+ifInNUcastPkts) END, ifInErrors=case WHEN (ifInUcastPkts+ifInNUcastPkts)= 0 THEN 0 ELSE ifInErrors/(ifInUcastPkts+ifInNUcastPkts) END, ifInUnknownProtos=case WHEN (ifInUcastPkts+ifInNUcastPkts)= 0 THEN 0 ELSE ifInUnknownProtos/(ifInUcastPkts+ifInNUcastPkts) END, ifOutOctets=case WHEN (ifSpeed*IntervalDuration)= 0 THEN 0 WHEN ifSpeed = 1 THEN 0 ELSE ifOutOctets*8.0/(ifSpeed*IntervalDuration) END, ifOutDiscards=case WHEN (ifOutUcastPkts+ifOutNUcastPkts)= 0 THEN 0 ELSE ifOutDiscards/(ifOutUcastPkts+ifOutNUcastPkts) END, ifOutErrors=case WHEN (ifOutUcastPkts+ifOutNUcastPkts)= 0 THEN 0 ELSE ifOutErrors/(ifOutUcastPkts+ifOutNUcastPkts) END, InterfaceView.ifSpeed FROM  M2ifStats, InterfaceView WHERE userId = 0 AND InterfaceView.server_addr=M2ifStats.server_addr AND InterfaceView.probe_addr=M2ifStats.probe_addr AND InterfaceView.rsrcIndex=M2ifStats.ifIndex
GO
GRANT SELECT ON  [dbo].[M2ifStatsThView] TO [uniadmin]
GO