?
Solved

Count unique max versions

Posted on 2008-06-18
12
Medium Priority
?
388 Views
Last Modified: 2013-11-21
Hi, the SQL statement below reports two lines from our SMS database as shown below

Microsoft Visio      10.0      96      
Microsoft Visio      11.0      56      

However, I know that out of the 96 V10.0 installations, 9 of them  have been upgraded to V11 so the actual figure should be 87.  The database however lists two entries for these upgraded machines, a line for V10 and a line for V11 so it's basically counting these 9 twice using the statement below/ above results.  

My question is - is there any way I can now modify the statement further to reduce the figure to 87 - basically asking it to take into account that it has been upgraded to V11 so is counted there, so not to count it twice!  Hope that makes sense!!   Many thanks in advance, Deborah.
SELECT     TOP (100) PERCENT dbo.vSMS_G_System_SoftwareProduct.ProductName, MAX(DISTINCT dbo.vSMS_G_System_SoftwareProduct.ProductVersion) 
                      AS Expr1, COUNT(DISTINCT dbo.vSMS_G_System_SoftwareProduct.ClientId) AS Expr2, dbo.vSMS_G_System_SoftwareProduct.ProductVersion
FROM         dbo.vSMS_G_System_SoftwareProduct INNER JOIN
                      dbo.v_R_System ON dbo.vSMS_G_System_SoftwareProduct.ClientId = dbo.v_R_System.ResourceID
GROUP BY dbo.vSMS_G_System_SoftwareProduct.ProductName, dbo.vSMS_G_System_SoftwareProduct.ProductVersion
HAVING      (dbo.vSMS_G_System_SoftwareProduct.ProductName = 'microsoft visio')
ORDER BY Expr2 DESC

Open in new window

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
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 22

Expert Comment

by:dportas
ID: 21812872
How can we tell if an application was upgraded or whether both versions are still in use? Assuming there is an UninstalledDate or UpgradedDate just somewhere then just put that into a WHERE clause.

Do not use TOP (100) PERCENT in any query or view. It achieves nothing but causes plenty of confusion. TOP (100) PERCENT is just an unfortunate side effect of the query/view designer.
0
 

Author Comment

by:happyappys
ID: 21813016
Hi, the Top(10) percent was put in by sql server 2005 automatically, it does it every time.  There is no installed date or upgraded date, sms just trawles round PC's looking at what sofware is installed and basically makes a list of it.  I have checked on one of the users PC's the only reason I can assume that there's two entries in the database is because when Microsoft upgrades visio it leaves some files of the old version behind which SMS is registering.  Thanks.
0
 
LVL 2

Accepted Solution

by:
AntonyDN earned 1000 total points
ID: 21813173
Hi,

This ought to do it, assuming your product version is numeric, or at least sequential in some way ....


SELECT ProductName
, ProductVersion
, COUNT(ClientId) AS NumOfInstallations
FROM 
	(SELECT dbo.vSMS_G_System_SoftwareProduct.ClientID
	, dbo.vSMS_G_System_SoftwareProduct.ProductName
	, MAX(dbo.vSMS_G_System_SoftwareProduct.ProductVersion) ProductVersion
	FROM dbo.vSMS_G_System_SoftwareProduct 
	INNER JOIN dbo.v_R_System
	ON dbo.vSMS_G_System_SoftwareProduct.ClientId = dbo.v_R_System.ResourceID
	WHERE  dbo.vSMS_G_System_SoftwareProduct.ProductName = 'microsoft visio'
	GROUP BY dbo.vSMS_G_System_SoftwareProduct.ClientId
	, dbo.vSMS_G_System_SoftwareProduct.ProductName
	)Z
GROUP BY ProductName, ProductVersion

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 11

Assisted Solution

by:aaronakin
aaronakin earned 1000 total points
ID: 21813185
It's not really pretty, but give it a shot...
SELECT a.ProductName, a.ProductVersion, ISNULL(SUM(NumClients),0) AS NumClients
  FROM
      (
      SELECT ssp.ProductName, ssp.ProductVersion
        FROM dbo.vSMS_G_System_SoftwareProduct ssp
          INNER JOIN dbo.v_R_System s ON ssp.ClientId = s.ResourceID
        GROUP BY ssp.ProductName, ssp.ProductVersion
      ) a
    LEFT OUTER JOIN
      (
      SELECT ProductName, LatestProductVersion, COUNT(ClientId) AS NumClients
        FROM
            (
            SELECT ssp.ProductName, ssp.ClientId, MAX(ssp.ProductVersion) AS LatestProductVersion
              FROM dbo.vSMS_G_System_SoftwareProduct ssp
                INNER JOIN dbo.v_R_System s ON ssp.ClientId = s.ResourceID
              GROUP BY ssp.ProductName, ssp.ClientId
              ) c
        GROUP BY ProductName, LatestProductVersion
        ) b
      ON a.ProductName = b.ProductName AND a.ProductVersion = b.LatestProductVersion
  WHERE a.ProductName = 'microsoft visio'
  GROUP BY a.ProductName, a.ProductVersion

Open in new window

0
 
LVL 11

Expert Comment

by:aaronakin
ID: 21813230
It looks like AntonyDN's query works as well...just beat me to it. :-)

The one thing I added to mine is that I return the number of clients for ALL product versions, whether any clients have that version or not.  Not even sure if you will need this functionality.
0
 

Author Comment

by:happyappys
ID: 21813310
WOW, thanks guys that's amazing!!  Just one last question then - if I want this query to show ALL software installed rather than just Visio, do I just take the WHERE clause out?
0
 
LVL 11

Expert Comment

by:aaronakin
ID: 21813316
Yep!
0
 

Author Comment

by:happyappys
ID: 21813323
Oh, and yes aaronakin - I need to see individual count of versions too for each software product!
0
 
LVL 2

Expert Comment

by:AntonyDN
ID: 21813342
Yes ....
0
 
LVL 2

Expert Comment

by:AntonyDN
ID: 21813536
I'm not quite sure what you are after there - I think both queries will give number of versions installed.

If you want to see,
Product                NumOfVersions
Microsoft Visio,    2
then that's a different query

Can you clarify?
0
 

Author Comment

by:happyappys
ID: 21813588
It's OK, just removing the where clause gives me the answer, a line for each product and version and the count of them, thanks very much that's fab!! Debs
0
 

Author Closing Comment

by:happyappys
ID: 31468333
Great!!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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