→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
GMFDE
Engaged Sweeper III
I have a chart report that works well but I want to refine it a little bit. The report queries a group of workstations, determines what version of java is installed and presents that in a bar chart with totals for each version. Currently, there are 30 aggregate versions displayed. I would like to reduce that to the 4 base versions I'm interested in tracking.

Select Top 1000000 tblSoftware.softwareVersion As Version,
Count(tblSoftware.AssetID) As Total
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1 And tblAssets.AssetName In ('USAOCULA7193MWT',
'asset1', 'asset2', 'asset...)
Group By tblSoftware.softwareVersion,
tblSoftwareUni.softwareName
Having tblSoftwareUni.softwareName Like 'java%'
Order By Version Desc


The result is a line for each sub-version for 10, 8, 7 and 6 like this
8.0.910.15 8
8.0.910.14 2
8.0.450 15

I would prefer it just display
8 25
7 12
6 7

I've tried a few variations in criteria with "where" statements and using different grouping settings.

Any advice would be appreciated. Thanks.
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Java's a bit of an odd duck of an example to use since the value of softwareName includes an update number that's associated with the full version number, but let's give this a shot.

Count the full softwareName values but display a truncated version number.
Select Top 1000000
tblSoftwareUni.softwareName,
Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) - 1) As Version,
Count(tblSoftware.AssetID) As Total
From
tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
tblAssetCustom.State = 1
AND tblSoftwareUni.softwareName Like 'java%'
Group By
tblSoftwareUni.softwareName,
Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) - 1)
Order By
Version Desc

On the other hand, if you just want the major version information from the softwareName -- e.g. you just want "Java 8" and don't care what update version -- you need to truncate the name. Something like this should get you closer to the target:
Select Top 1000000
CASE
WHEN CharIndex(' Update', tblSoftwareUni.softwareName) > 0
THEN LEFT(tblSoftwareUni.softwareName, CharIndex(' Update', tblSoftwareUni.softwareName)-1)
ELSE tblSoftwareUni.softwareName
END AS SoftwareName,
Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) - 1) As Version,
Count(tblSoftware.AssetID) As Total
From
tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
tblAssetCustom.State = 1
AND tblSoftwareUni.softwareName Like 'java%'
Group By
CASE
WHEN CharIndex(' Update', tblSoftwareUni.softwareName) > 0
THEN LEFT(tblSoftwareUni.softwareName, CharIndex(' Update', tblSoftwareUni.softwareName)-1)
ELSE tblSoftwareUni.softwareName
END,
Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) - 1)

View solution in original post

5 REPLIES 5
RCorbeil
Honored Sweeper II
Java's a bit of an odd duck of an example to use since the value of softwareName includes an update number that's associated with the full version number, but let's give this a shot.

Count the full softwareName values but display a truncated version number.
Select Top 1000000
tblSoftwareUni.softwareName,
Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) - 1) As Version,
Count(tblSoftware.AssetID) As Total
From
tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
tblAssetCustom.State = 1
AND tblSoftwareUni.softwareName Like 'java%'
Group By
tblSoftwareUni.softwareName,
Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) - 1)
Order By
Version Desc

On the other hand, if you just want the major version information from the softwareName -- e.g. you just want "Java 8" and don't care what update version -- you need to truncate the name. Something like this should get you closer to the target:
Select Top 1000000
CASE
WHEN CharIndex(' Update', tblSoftwareUni.softwareName) > 0
THEN LEFT(tblSoftwareUni.softwareName, CharIndex(' Update', tblSoftwareUni.softwareName)-1)
ELSE tblSoftwareUni.softwareName
END AS SoftwareName,
Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) - 1) As Version,
Count(tblSoftware.AssetID) As Total
From
tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
tblAssetCustom.State = 1
AND tblSoftwareUni.softwareName Like 'java%'
Group By
CASE
WHEN CharIndex(' Update', tblSoftwareUni.softwareName) > 0
THEN LEFT(tblSoftwareUni.softwareName, CharIndex(' Update', tblSoftwareUni.softwareName)-1)
ELSE tblSoftwareUni.softwareName
END,
Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) - 1)
GMFDE
Engaged Sweeper III
RC62N wrote:
Java's a bit of an odd duck of an example to use since the value of softwareName includes an update number that's associated with the full version number, but let's give this a shot.

On the other hand, if you just want the major version information from the softwareName -- e.g. you just want "Java 8" and don't care what update version -- you need to truncate the name. Something like this should get you closer to the target:
Select Top 1000000
CASE
WHEN CharIndex(' Update', tblSoftwareUni.softwareName) > 0
THEN LEFT(tblSoftwareUni.softwareName, CharIndex(' Update', tblSoftwareUni.softwareName)-1)
ELSE tblSoftwareUni.softwareName
END AS SoftwareName,
Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) - 1) As Version,
Count(tblSoftware.AssetID) As Total
From
tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
tblAssetCustom.State = 1
AND tblSoftwareUni.softwareName Like 'java%'
Group By
CASE
WHEN CharIndex(' Update', tblSoftwareUni.softwareName) > 0
THEN LEFT(tblSoftwareUni.softwareName, CharIndex(' Update', tblSoftwareUni.softwareName)-1)
ELSE tblSoftwareUni.softwareName
END,
Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) - 1)


The second proposed solution hit the mark exactly. I've added this to our codebase. Thanks very much indeed.
RCorbeil
Honored Sweeper II
LEFT(tblSoftware.softwareVersion, CHARINDEX('.', tblSoftware.softwareVersion)-1)

If there's a chance that some of what you're reporting on won't have a decimal point in the softwareVersion value, you'll need to make it conditional.

SQL Server string functions at w3schools.com.
T-SQL string functions at Microsoft.
GMFDE
Engaged Sweeper III
I'm sure it's just a syntax error on my part but when I run the below, I get "This report has no results!" Again, I'm sure that I'm using your code incorrectly. I appreciate your patience but if you wouldn't mind, a pointer in the right direction would be greatly appreciated.

Select Top 1000000 tblSoftware.softwareVersion As Version,
Count(tblSoftware.AssetID) As Total
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblSoftware.softwareVersion,
tblSoftwareUni.softwareName
Having tblSoftware.softwareVersion = Left(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) - 1) And
tblSoftwareUni.softwareName Like 'java%'
Order By Version Desc


GMFDE
Engaged Sweeper III
RC62N wrote:
LEFT(tblSoftware.softwareVersion, CHARINDEX('.', tblSoftware.softwareVersion)-1)

If there's a chance that some of what you're reporting on won't have a decimal point in the softwareVersion value, you'll need to make it conditional.

SQL Server string functions at w3schools.com.
T-SQL string functions at Microsoft.


Testing, will update shortly. Thanks much.