Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

select statement required for distinct rows from SMS Database

Posted on 2008-06-18
7
Medium Priority
?
747 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 500 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

610 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