Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 754
  • Last Modified:

select statement required for distinct rows from SMS Database

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!!
0
happyappys
Asked:
happyappys
  • 4
  • 3
1 Solution
 
MilleniumaireCommented:
Try this:

select SMS_R_System.ResourceId,
           SMS_R_System.Name,
           SMS_G_System_SoftwareProduct.ProductName,
           max(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"
group by SMS_R_System.ResourceId,
               SMS_R_System.Name,
               SMS_G_System_SoftwareProduct.ProductName
order by SMS_R_System.ResourceId
0
 
happyappysAuthor Commented:
Hi, thanks for this, however when I enter the query in it tells me there's a syntax error?
0
 
MilleniumaireCommented:
I can't run this against my database as I don't have your tables.  Does the error give you an indication of where the syntax is incorrect?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
happyappysAuthor Commented:
Hi again, it seems that I can enter this as a query in the Reporting section of SMS but not in the Query section of SMS.  Unfortunatley our main man has just restricted my access by mistake and I'm waiting for it to reinstate.  I've tried in Enterprise Manager and it works so ignore the syntax error.  If I also wanted to now just show the versions currently on V11 how would this change?  Once again many thanks for your assistance!
0
 
happyappysAuthor Commented:
This seems to work with the right results:-

SELECT     TOP (100) PERCENT dbo.v_R_System.ResourceID, dbo.v_R_System.Name0, dbo.vSMS_G_System_SoftwareProduct.ProductName,
                      MAX(dbo.vSMS_G_System_SoftwareProduct.ProductVersion) AS Expr1
FROM         dbo.v_R_System INNER JOIN
                      dbo.vSMS_G_System_SoftwareProduct ON dbo.v_R_System.ResourceID = dbo.vSMS_G_System_SoftwareProduct.ClientId
WHERE     (dbo.vSMS_G_System_SoftwareProduct.ProductName = 'Microsoft Visio')
GROUP BY dbo.v_R_System.ResourceID, dbo.v_R_System.Name0, dbo.vSMS_G_System_SoftwareProduct.ProductName
HAVING      (MAX(dbo.vSMS_G_System_SoftwareProduct.ProductVersion) = '10.0')
ORDER BY dbo.v_R_System.ResourceID

Unless there's anything obvious, if I just change the HAVING criteria to 10.0 for the V10 Query and 11.0 for the V11 query I'm guessing this all looks OK?
0
 
MilleniumaireCommented:
Hi happyappys, your query looks okay to me.  
0
 
happyappysAuthor Commented:
Great stuff, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now