I'm developing this query for a dashboard using System Center Config Manager Dashboard. The query needs to produce a table which shows the number of computers which have Office 2007 Standard edition in one row, and the next row should show the number of computers which do not have it. The dashboard takes this table of info and puts it into a pie chart - but I can't get my calculations to work right. I'm not very familiar with case statements and would say I'm maybe an beginner/intermediate level with sql so this query is a bit advanced for me with the case statements and calculations.
Need to be able to count the number of unique machines (ResourceID) where DisplayName = Microsoft Office Standard 2007, then count either those machines without this version OR the total number of unique machines left in that table. I think I might be close? Any pointers are greatly appreciated!
declare @Office table (DisplayName varchar(140), ResourceID varchar(10), TotalTarget decimal, Office2007StdPct decimal) insert into @Office(DisplayName, ResourceID, TotalTarget, Office2007StdPct) select arp.DisplayName0, (case when arp.DisplayName0='Microsoft Office Standard 2007') as ResourceID2007, (case when arp.DisplayName0=, count(*) as TotalTarget, round(100.0*count(arp.ResourceID)/count(*),1) as Office2007StdPct From v_Add_Remove_Programs arp inner join v_CM_RES_COLL_MI100017 coll on arp.ResourceID=coll.ResourceID inner join v_RA_System_SMSAssignedSites sas on coll.ResourceID=sas.ResourceID where sas.SMS_Assigned_Sites0=@Site group by arp.DisplayName0, arp.ResourceID select ' Upgraded %' as UpgradeStatus, cast((Office2007StdPct) as numeric(10,0)) as TotalPercent from @Office where DisplayName ='Microsoft Office Standard 2007' union select 'Not Upgraded %' as UpgradeStatus, cast((100-(Office2007StdPct)) as numeric(10,0)) as TotalPercent from @Office where DisplayName like 'Microsoft Office 2000 S%'or DisplayName like 'Microsoft Office Standard%'or DisplayName like 'Microsoft Office Professional%'