Member Avatar for sdimantova

Hi,
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%'
  • 3 Contributors
  • 6 Replies
  • 164 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by sdimantova
Member Avatar for urtrivedi

I have changed your line no 2

SELECT arp.DisplayName0, 
(case when arp.DisplayName0='Microsoft Office Standard 2007' THEN arp.DisplayName0 ELSE '' END) AS ResourceID2007, 
count(*) AS TotalTarget, 
round(100.0*count(case when arp.DisplayName0='Microsoft Office Standard 2007' THEN 1 ELSE 0 END)/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
Edited by urtrivedi because: n/a
Member Avatar for sdimantova

It doesn't give any errors but the code provided doesn't return anything - even when I try it by itself without the table declaration and union at the end, the results seem to be empty...Not sure why that is?

How can I just create one variable that counts the total number of 2007 Standard, and then a total count of the machines in the table, and then I can subtract one from the other to get the percentage at the end?

Member Avatar for willsee99

Few little issues...

First the case statement is typically :
case when <condition> then <value if true> else <value if false> end

Next you have group by statements, so anything not part of the group by needs to be an aggregate function.

So, assuming we do need some counters, thought a slightly more simple approach might be worthwhile.

Have a look at :

declare @Office TABLE (DisplayName varchar(140), ResourceID varchar(10), Count2007 decimal(10,1), countOther decimal(10,1), TotalTarget decimal(10,1))

insert into @Office(DisplayName, ResourceID, Count2007, CountOther, TotalTarget)

select arp.DisplayName0, arp.resourceID
, sum(case when arp.DisplayName0='Microsoft Office Standard 2007' then 1.0 else 0.0 end) as Count2007
, sum(case when arp.DisplayName0<>'Microsoft Office Standard 2007' then 1.0 else 0.0 end) as CountOther
, count(*) as TotalTarget
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, round( (sum(Count2007)/sum(totaltarget))*100,1) as TotalPercent from @Office 
union
select 'Not Upgraded %' as UpgradeStatus, round( (sum(CountOther)/sum(totaltarget))*100,1) as TotalPercent from @Office
Member Avatar for sdimantova

The code you provided works EXCEPT the resulting table has no values. I tried removing the sum function from (sum(totaltarget)) and when I did that it gave me a syntax error:
Column '@Office.TotalTarget' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any ideas? Unfortunately my limited knowledge prevents me from doing any legit troubleshooting.

Member Avatar for urtrivedi

You post your sample data from all tables that you include in your query with column name.
Then post expected output of query.

Member Avatar for sdimantova

Add_Remove_Programs table contains a list of all programs discovered in Add/Remove programs on each machine, including version number, title of program, and then machine name. v_CM_RES_COLL_MI100017 table represents a collection of machines I created, and v_RA_System_SMSAssignedSites contains every machine with its' assigned site code based on the location of the machine (we have 3 separate branches, only want to deal with one branch).

The result I'm looking for will have one row which counts the number of machines which have Office 2007 Standard in their Add/Remove Programs as a percentage of the total number of machines. Then the next row will count the number of machines without Office 2007 as a percentage of total machines. The total of these two numbers should obviously be 100%, because then the program I'm using turns the resulting mini-table into a pie chart.

Below is the query as I have it right now - the resulting table gives machines with Office 2007 100% and those without 0%, which is not correct. Anyone have any ideas how to fix the math?

declare @Office table (DisplayName varchar(140), ResourceID varchar(10), TotalTarget decimal, Office2007StandardPct decimal)
insert into @Office(DisplayName, ResourceID, TotalTarget, Office2007StandardPct)
select arp.DisplayName0, coll.ResourceID, count(*) as TotalTarget, round(100.0*count(coll.ResourceID)/count(*),1) as Office2007StandardPct
From v_Add_Remove_Programs arp
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
and arp.DisplayName0='Microsoft Office Standard 2007'
group by arp.DisplayName0, coll.ResourceID
select ' Upgraded %' as UpgradeStatus, cast(avg(Office2007StandardPct) as numeric(10,0))  as TotalPercent from @Office 
union
select 'Not Upgraded %' as UpgradeStatus, cast((100-(avg(Office2007StandardPct))) as numeric(10,0)) as TotalPercent from @Office
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.