select statement required for distinct rows from SMS Database
Posted on 2008-06-18
Hi, We have SMS installed which is sat on a SQL Backend. We want to report on all the PC's in our organisation with Microsoft Visio installed. SMS however stores a line for the PC with Visio Version 10 and a line for Version 11 if it has had both versions installed. I want to create a query that shows me one report for all those NOW on version 11 (regardless of whether they have a line for version 10) and one report for all those STILL on version 10 (ie no line with V11 on). Ideally I would also like to count these but would like to deal with that separately.
The current query below returns all the lines of PC's and within that data are duplicate PC's where they have a Version 10 and 11 installed. These amount to 154. I know however that there are actually only 145 PC's with Visio on so 9 have been upgraded and are showing two lines. The code is as follows:-
select distinct SMS_R_System.ResourceId, SMS_R_System.Name, SMS_G_System_SoftwareProduct.ProductName, SMS_G_System_SoftwareProduct.ProductVersion from SMS_R_System inner join SMS_G_System_SoftwareProduct on SMS_G_System_SoftwareProduct.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareProduct.ProductName = "Microsoft Visio" order by SMS_R_System.ResourceId
Any help would greatly be appreciated!!