Solved

select statement required for distinct rows from SMS Database

Posted on 2008-06-18
7
727 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now