I am writing a report that will display what versions of a particular software is compatible with which versions of Windows 10. I have matrix that was give to me by the vendor. Below is part of my query.

In the first case when I am linking the Windows version with the build number to make the results easier to read.

Select Top 1000000 tblAssets.AssetID,

tblAssets.AssetName,

tsysAssetTypes.AssetTypeIcon10 As icon,

tblOperatingsystem.Version As Build,

Case

When tblOperatingsystem.Version Like ‘10.0.10240’ Then ‘1507’

When tblOperatingsystem.Version Like ‘10.0.10586’ Then ‘1511’

When tblOperatingsystem.Version Like ‘10.0.14393’ Then ‘1607’

When tblOperatingsystem.Version Like ‘10.0.15063’ Then ‘1703’

When tblOperatingsystem.Version Like ‘10.0.16299’ Then ‘1709’

When tblOperatingsystem.Version Like ‘10.0.17134’ Then ‘1803’

When tblOperatingsystem.Version Like ‘10.0.17763’ Then ‘1809’

When tblOperatingsystem.Version Like ‘10.0.18362’ Then ‘1903’

When tblOperatingsystem.Version Like ‘10.0.18363’ Then ‘1909’

When tblOperatingsystem.Version Like ‘10.0.19041’ Then ‘2004’

End version,

Case

When tblOperatingsystem.Version Like ‘1607’ And

tblSoftware.softwareVersion Like ‘5.4.%’ Or

tblSoftware.softwareVersion Like ‘5.3.%’ Or

tblSoftware.softwareVersion Like ‘5.2.%’ Or

tblSoftware.softwareVersion Like ‘5.1.%’ Or

tblSoftware.softwareVersion Like ‘5.0.%’ Or

tblSoftware.softwareVersion Like ‘4.1.%’ Or

tblSoftware.softwareVersion Like ‘4.9.%’ Or

tblSoftware.softwareVersion Like ‘4.8.%’ Or

tblSoftware.softwareVersion Like ‘4.7.%’ Or

tblSoftware.softwareVersion Like ‘4.6.%’ Then ‘Not Compatable’

End compat3,

Case

When tblOperatingsystem.Version Like ‘1809’ And

tblSoftware.softwareVersion Like ‘5.4.%’ Or

tblSoftware.softwareVersion Like ‘4.8.%’ Or

tblSoftware.softwareVersion Like ‘4.7.%’ Or

tblSoftware.softwareVersion Like ‘4.6.%’ Or

tblSoftware.softwareVersion Like ‘4.5.%’ Or

tblSoftware.softwareVersion Like ‘4.4.%’ Then ‘Not Compatable’

End compat2,

tblSoftware.softwareVersion,

tblSoftwareUni.softwareName

From tblAssets

Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID

Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype

Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID

Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID

Inner Join tblOperatingsystem On

tblAssets.AssetID = tblOperatingsystem.AssetID

Where tblSoftwareUni.softwareName = ‘vmware horizon client’ And

tblOperatingsystem.Caption = ‘Microsoft Windows 10 Pro’ And

tblAssetCustom.State = 1

For some reason the second case statement is putting not compatible on the Windows 1809 as well as 1607. The first case should not even be concerned with 1809. Any ideas?