CREATE VIEW [dbo].[allpowercomputers]
        AS
        SELECT    DISTINCT dbo.ai_oldef_prop.userid,
            dbo.ai_oldef_prop.orgnm,
            dbo.ai_oldef_prop.office,
            dbo.ai_oldef_prop.hwsource,
            dbo.ai_oldef_prop.region,
            dbo.ai_oldef_prop.osname,
            dbo.ai_oldef_prop.ossp,
            dbo.ai_oldef_prop.osver,
            dbo.ai_oldef_prop.ospub,
            dbo.ai_oldef_prop.oslanguage,
            dbo.ai_oldef_prop.hwuse,
            ai_oldef_prop.fssize,
            ai_oldef_prop.fsfree,
            dbo.ai_oldef_prop.instdate,
            dbo.ai_oldef_prop.lscandate,
            dbo.ai_hwdef_prop.*,
            round(round(convert(decimal(5,2),coreavg/10),0)*10,0) as hwcpuperf,
            ROUND(ROUND(CONVERT(decimal(5, 2), dbo.ai_hwdef_cpuperf_kpi.earlyavg / 10), 0)
            * 10, 0) AS hwcpuearlyperf, ROUND(ROUND(CONVERT(decimal(5, 2), dbo.ai_hwdef_cpuperf_kpi.lateavg / 10), 0)
            * 10, 0) AS hwcpulateperf,
            ai_pwrscheme_prop_rel.pwrschuuid,
            ai_pwrscheme_prop.pwrschname,
            ai_pwrscheme_prop_rel.turn_off_monitor_ac,
            ai_pwrscheme_prop_rel.turn_off_hdd_ac,
            ai_pwrscheme_prop_rel.standby_ac            
        FROM    dbo.ai_hwdef_prop
            INNER JOIN
            dbo.ai_oldef_prop
            ON dbo.ai_hwdef_prop.hwuuid = dbo.ai_oldef_prop.hwuuid
            LEFT JOIN
            ai_hwdef_cpuperf_kpi
            ON ai_hwdef_prop.hwuuid = ai_hwdef_cpuperf_kpi.hwuuid
            LEFT JOIN
            ai_pwrscheme_prop_rel
            ON ai_pwrscheme_prop_rel.hwuuid = ai_hwdef_prop.hwuuid         
            INNER JOIN
            ai_pwrscheme_prop    
            ON ai_pwrscheme_prop_rel.hwuuid = ai_hwdef_prop.hwuuid AND ai_pwrscheme_prop_rel.pwrschuuid = ai_pwrscheme_prop.pwrschuuid
        WHERE     ai_hwdef_prop.hwname != ''
GO
GRANT SELECT ON  [dbo].[allpowercomputers] TO [aiadmin]
GRANT INSERT ON  [dbo].[allpowercomputers] TO [aiadmin]
GRANT DELETE ON  [dbo].[allpowercomputers] TO [aiadmin]
GRANT UPDATE ON  [dbo].[allpowercomputers] TO [aiadmin]
GRANT SELECT ON  [dbo].[allpowercomputers] TO [aipublic]
GO