Chart report grouping criteria

[GMFDE]

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.

[RCorbeil · Accepted solution]

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]

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]

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]

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]

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.