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