Solved

select statement required for distinct rows from SMS Database

Posted on 2008-06-18
7
741 Views
Last Modified: 2013-11-21
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
Comment
Question by:happyappys
  • 4
  • 3
7 Comments
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 125 total points
ID: 21811334
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
 

Author Comment

by:happyappys
ID: 21811506
Hi, thanks for this, however when I enter the query in it tells me there's a syntax error?
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21811544
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:happyappys
ID: 21812086
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
 

Author Comment

by:happyappys
ID: 21812146
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21812238
Hi happyappys, your query looks okay to me.  
0
 

Author Closing Comment

by:happyappys
ID: 31468247
Great stuff, thanks!
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question