Join the results of two different columns in the same one

[borgoncete88]

Hi all,

I have made a custom report to check if the AV and OS are updated. So my problem is when I'm getting the information from Windows versions, I only got it using two columns, one for Win 10 and the other for Win 11. If version of Win 10 is different from 22H2 is not updated and if the version of Win 11 is different from 23H2 is not updated too. What I want is to join the information from these columns in the same. I don't mind if the asset is Win 11 or 10, I want to check the OS version for every asset in the same column. If anyone can help me it would very  useful. Thank you in advance.

This is my code:

Select Distinct Top 1000000 unioned.assetid,
tblAssets.AssetName,
tblAssets.Username,
tsysOS.Image As icon,
unioned.software,
unioned.Enabled,
tblSoftware.softwareVersion,
Case
When tblSoftware.softwareVersion != '8.0.22' Then 'NO'
Else 'SI'
End As [¿AV Actualizado?],
tblAssets.IPAddress,
tsysOS.OSname As OS,
tblAssets.Version,
Case
When tsysOS.OSname = 'Win 10' And tblAssets.Version != '22H2' Then 'NO'
Else 'SI'
End As [¿OS Win 10 Actualizado?],
Case
When tsysOS.OSname = 'Win 11' And tblAssets.Version != '23H2' Then 'NO'
Else 'SI'
End As [¿OS Win 11 Actualizado?],
tblAssets.Lastseen
From ((Select a.assetid As assetid,
a.software As software,
a.version As version,
'software comparison' As RetrievedFrom,
'' As Enabled,
'' As Uptodate
From (Select tblSoftware.AssetID As assetid,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) a)
Union
(Select tblAntivirus.AssetID As assetid,
tblAntivirus.DisplayName As software,
Null As version,
'WMI' As RetrievedFrom,
Case
When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes'
Else 'No'
End As Enabled,
Case
When tblAntivirus.productUpToDate = 1 Then 'Yes'
Else 'No'
End As Uptodate
From tblAntivirus)) unioned
Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Where (unioned.software = 'WatchGuard EPDR' And unioned.Enabled = 'Yes' And
tblSoftware.softwareVersion = '8.0.22') Or
(tblSoftware.softwareVersion = '8.0.21')
Order By tblAssets.AssetName Desc

[borgoncete88]

Hi @Hendrik_VE,

It works perfectly, thank you!

What a noob I have been... It was so easy to solve it, but I didn't know I can use two "When" in the same "Case".

Greetings.

[Hendrik_VE · Accepted solution]

Replace the two "case" statements with this one:

Case
When tsysOS.OSname = 'Win 10' And tblAssets.Version = '22H2' Then 'SI'
When tsysOS.OSname = 'Win 11' And tblAssets.Version = '23H2' Then 'SI'
Else 'NO'
End As [¿OS Actualizado?]