cancel
Showing results forĀ 
ShowĀ Ā onlyĀ  | Search instead forĀ 
Did you mean:Ā 
arch
Engaged Sweeper

I am trying to create a report that shows systems that have an application installed "x" and when installed the version is not "y" or "z".  I have several of these type reports that work fine when I am only looking at one version.  With the version being a string and not a number I can't use greater than to evaluate.  Below is what I have but it returns no systems, and I am 100% sure there are systems that don't have either version of the application installed.  Does anyone know how to make this work, assuming it is even possible?

 

Thank you very much.

 

From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblSoftwareUni.softwareName Like 'x' And
tblSoftware.softwareVersion Not Like 'y%' And
tblSoftware.softwareVersion Not Like 'z%' And tblAssetCustom.State = 1

17 REPLIES 17
rom
Champion Sweeper III

what is the full version standard?  X.X.X.X , or X.X.X ? 

Thanks

Mister_Nobody
Honored Sweeper II

Try this:

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tblassets.IPAddress,
  tblassets.Lastseen,
  tblassets.Lasttried,
  tblSoftwareUni.softwareName
From tblassets
  Inner Join tblSoftware On tblassets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblassets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
    From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where (tblSoftwareUni.softwareName Like '%finereader 9%' Or
        tblSoftwareUni.softwareName Like '%finereader 11%')) And
  tblSoftwareUni.softwareName Like '%finereader 12%'

 

 

unfortunately for the application I am trying to generate the report on, the version is not stored with the software name so including the version with the name did not work.

Mister_Nobody
Honored Sweeper II

You can change code of sql to version 

ok, I tried this as well and it too returns nothing.  When I try to add the extra parentheses that your response has, the report editor just removes them.

Where tblSoftwareUni.softwareName Like 'x' And
(tblSoftware.softwareVersion Not Like 'y%' Or
tblSoftware.softwareVersion Not Like 'z%') And tblAssetCustom.State = 1

Mister_Nobody
Honored Sweeper II

Don't use 'not like' with 'or'

Mister_Nobody
Honored Sweeper II

You should use my query logics. 

Please send full code not cut

I apologize for not understanding your query logic.  I thought I was correctly interpreting your suggestion and updating the query.  Below is the full code of the query.

 

Select Top 1000000 tblAssets.AssetID,
tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftware.Installdate,
tblAssets.Lastseen,
tblAssets.Username,
tblAssets.IPAddress
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblSoftwareUni.softwareName Like 'x' And
(tblSoftware.softwareVersion Not Like 'y%' Or
tblSoftware.softwareVersion Not Like 'z%') And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

Mister_Nobody
Honored Sweeper II

Obviously,

expression (tblSoftware.softwareVersion Not Like 'y%' Or tblSoftware.softwareVersion Not Like 'z%') is always true.