Office SQL

-- Counts Office version

select 

count(Total.Name0) as Total_Systems,COUNT(office2003.Name0) as MSoffice2003,COUNT(Office2007.Name0) as MSOffice2007,COUNT(Office2010.Name0) as MsOffice2010, COUNT(Office2013.Name0) as MsOffice2013

From 

(select vr.Name0 from v_R_System vr) 

as Total 

left join 

(select distinct sys.Name0, 

      displayname00 as OfficeEdition, 

      version00 as OfficeVersion  from system_disc sys 

left join 

add_remove_programs_data arp on sys.itemkey = arp.machineid where 

(displayname00 = 'Microsoft Office Basic Edition 2003' 

OR displayname00 = 'Microsoft Office Excel 2003' 

OR displayname00 = 'Microsoft Office Outlook 2003' 

OR displayname00 = 'Microsoft Office Personal Edition 2003' 

OR displayname00 = 'Microsoft Office Professional Edition 2003' 

OR displayname00 = 'Microsoft Office Small Business Edition 2003' 

OR displayname00 = 'Microsoft Office Standard Edition 2003') 

and client0 = 1)as office2003 

on Total.Name0=office2003.Name0 

left join 

(Select distinct sys.Name0, 

      displayname00 as OfficeEdition, 

      version00 as OfficeVersion  from system_disc sys 

left join 

add_remove_programs_data arp on sys.itemkey = arp.machineid where 

(displayname00 like 'Microsoft Office Personal 2007' 

or displayname00 like 'Microsoft Office Professional 2007' 

or displayname00 like 'Microsoft Office Professional 2007 Trial' 

or displayname00 like 'Microsoft Office Professional Hybrid 2007' 

or displayname00 like 'Microsoft Office Professional Plus 2007' 

or displayname00 like 'Microsoft Office Professional Plus 2007 (Beta)' 

or displayname00 like 'Microsoft Office Standard 2007' 

or displayname00 like 'Microsoft Office Standard 2007 Trial' 

or displayname00 like 'Microsoft Office Ultimate 2007') 

and sys.client0 = 1)as Office2007 

on Total.Name0=office2007.Name0 

left join 

(select distinct sys.Name0, 

      displayname00 as OfficeEdition, 

      version00 as OfficeVersion  from system_disc sys 

left join 

add_remove_programs_data arp on sys.itemkey = arp.machineid where 

(displayname00 like 'Microsoft Office 2010' 

or displayname00 like 'Microsoft Office Professional Plus 2010' 

or displayname00 like 'Microsoft Office Standard 2010' 

or displayname00 like 'Microsoft Office Professional 2010' 

or displayname00 like 'Microsoft Office Home and Student 2010' 

or displayname00 like 'Microsoft Office Home and Business 2010' 

or displayname00 like 'Microsoft Office Professional Plus 2010 (Beta)' 

or displayname00 like 'Microsoft Office Starter 2010 – English') 

and sys.client0 = 1) as office2010 

on Total.Name0=office2010.Name0

left join

(select distinct sys.Name0, 

      displayname00 as OfficeEdition, 

      version00 as OfficeVersion  from system_disc sys 

left join 

add_remove_programs_data arp on sys.itemkey = arp.machineid where 

(displayname00 like 'Microsoft Office Professional Plus 2013'  )

--or displayname00 like 'Microsoft Office Professional Plus 2013' 

and sys.client0 = 1) as office2013 

on Total.Name0=office2013.Name0