Notification

Icon
Error

Report to compare softwareVersion - Here is a report for comparing software versions.

Posted: Monday, December 16, 2019 4:50:37 PM(UTC)
Martin P

Martin P

Member Original PosterPosts: 2
0
Like
This report returns all computers with Office versions whose third number is less than 12228.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tsysOS.Image As icon,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
' ' As _,
Convert(bigint,Stuff(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion), Len(tblSoftware.softwareVersion) -
CharIndex('.', tblSoftware.softwareVersion) + 1, '')) As A1,
Convert(bigint,Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1), CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1)),
Len(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1)) - Len(CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1))), '')) As B1,
Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1)), '')),
Len(Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1)), '')) -
Len(CharIndex('.', Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1)), ''))), '')) As C1,
Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), 1, CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) +
1 - CharIndex('.', tblSoftware.softwareVersion) + 1)), '')), '')) As D1,
' ' As __,
Case
When Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')),
Len(Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')) - Len(CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''))), '')) <
12228 Then 'C1 kleiner'
When Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')),
Len(Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')) - Len(CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''))), '')) = 12228 And
Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), 1, CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')), '')) >
20332 Then 'C1 gleich, D1 groesser'
When Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')),
Len(Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')) - Len(CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''))), '')) = 12228 And
Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), 1, CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')), '')) =
20332 Then 'C1 gleich, D1 gleich pruef'
When Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')),
Len(Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')) - Len(CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''))), '')) = 12228 And
Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), 1, CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')), '')) <
20332 Then 'C1 gleich, D1 kleiner' End As [Version pruef],
' ' As ___,
tblAssets.IPAddress,
tsysOS.OSname As OS
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%Microsoft Office 365%' And
((Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')),
Len(Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')) -
Len(CharIndex('.', Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''))), '')) = 12228 And
Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), 1, CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')), '')) < 20332) Or
(Convert(bigint,Stuff(Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''), CharIndex('.',
Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')),
Len(Stuff(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), '')) -
Len(CharIndex('.', Stuff(SubString(tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1,
Len(tblSoftware.softwareVersion) + 1 - CharIndex('.',
tblSoftware.softwareVersion) + 1), 1, CharIndex('.',
SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, Len(tblSoftware.softwareVersion) + 1 -
CharIndex('.', tblSoftware.softwareVersion) + 1)), ''))), '')) < 12228))
And tsysOS.OSname Like 'Win 10'
Order By tblAssets.AssetName
RC62N
#1RC62N Member Posts: 433  
posted: 1/22/2020 12:03:15 AM(UTC)
Were it me, I would want to simplify for legibility and isolate the version-segment conversion so that it only has to be done once.

Given, if you know for certain that the version is formatted A.B.C.D:
Code:
Decimal 1: CharIndex('.', tblSoftware.softwareVersion)
Decimal 2: CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)
Decimal 3: CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)+1)


Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysOS.Image As icon,
  Software.SoftwareName,
  Software.SoftwareVersion,
  ' ' As _,
  Software.versionA,
  Software.versionB,
  Software.versionC,
  Software.versionD,
  ' ' As __,
  Case
  When Software.versionC < 12228 Then 'C1 kleiner'
  When Software.versionC = 12228 And
       Software.versionD > 20332 Then 'C1 gleich, D1 groesser'
  When Software.versionC = 12228 And
       Software.versionD = 20332 Then 'C1 gleich, D1 gleich pruef'
  When Software.versionC = 12228 And
       Software.versionD < 20332 Then 'C1 gleich, D1 kleiner' End As [Version pruef],
  ' ' As ___,
  tblAssets.IPAddress,
  tsysOS.OSname As OS
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select
               tblSoftware.AssetID,
               tblSoftware.SoftwareVersion,
               tblSoftwareUni.softwareName,
               tblSoftwareUni.SoftwarePublisher,

               Convert(BigInt, Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)-1)) AS versionA,

               Convert(BigInt,
                       Substring(tblSoftware.softwareVersion,
                                 CharIndex('.', tblSoftware.softwareVersion)+1,                                              -- next char after first decimal
                                 CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)  -- second decimal position
                                   - CharIndex('.', tblSoftware.softwareVersion)                                             -- minus first decimal position
                                   - 1                                                                                       -- don't want the second decimal
                                )
                      )  AS versionB,

               Convert(BigInt,
                       Substring(tblSoftware.softwareVersion,
                                 CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1) +1,                                             -- next char after second decimal
                                 CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)+1)  -- third decimal position
                                   - CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)                                             -- minus second decimal position
                                   - 1                                                                                                                                      -- don't want the third decimal
                                )
                      ) AS versionC,

               Convert(BigInt,
                       Right(tblSoftware.softwareVersion,
                             Len(tblSoftware.softwareVersion) - CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)+1)
                            )
                      ) AS versionD

             From
               tblSoftware
               Inner Join tblSoftwareUni On TblSoftwareUni.SoftID = tblSoftware.softID
             Where
               tblSoftwareUni.softwareName Like '%Microsoft Office 365%'
          ) AS Software ON Software.AssetID = tblAssets.AssetID

Where
      ( (     Software.versionC = 12228
          And Software.versionD < 20332
        )
        Or
        ( Software.versionC < 12228 )
      )
     And tsysOS.OSname LIKE 'Win 10'
Order By
  tblAssets.AssetName

(I think I matched the comparisons, but I'm not going to swear to it. I wasn't prepared to wade through the long chunks of code to figure out for certain exactly what match was being attempted. If I got it wrong, I think you should be able to figure out what the correction should be.)
RC62N
#2RC62N Member Posts: 433  
posted: 1/22/2020 4:57:16 PM(UTC)
If you don't know for certain that the version "number" will be in the form A.B.C.D, it would make sense to make the slicing logic a little more robust. It would also be safer not to assume that any of the "numbers" between the decimals are actually numbers, so eliminate the conversion to numeric; leave that to the outer select if it's warranted.

With that said, this should reliably slice up version "numbers" of the form
  • (blank/NULL)
  • A
  • A.B
  • A.B.C
  • A.B.C.D
returning the results as strings.
Code:
Select
  tblSoftware.AssetID,
  tblSoftware.SoftwareVersion,
  tblSoftwareUni.softwareName,
  tblSoftwareUni.SoftwarePublisher,

  CASE
    WHEN  -- IF there is a decimal
      CharIndex('.', tblSoftware.softwareVersion) > 0
    THEN  -- THEN return everything to the left of it
      Left(tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)-1)
    ELSE  -- ELSE just return the version
      tblSoftware.softwareVersion
  END AS versionA,

  CASE
    WHEN  -- IF there is a second decimal
      CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1) > 0
    THEN  -- THEN return what's between the first and second decimals
      Substring(tblSoftware.softwareVersion,
                CharIndex('.', tblSoftware.softwareVersion)+1,                                              -- next char after first decimal
                CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)  -- second decimal position
                  - CharIndex('.', tblSoftware.softwareVersion)                                             -- minus first decimal position
                  - 1                                                                                       -- don't want the second decimal
               )
    WHEN  -- ELSE if there's a first decimal
      CharIndex('.', tblSoftware.softwareVersion) > 0
    THEN  -- THEN return everything to the right of the first (only) decimal
      Right(tblSoftware.softwareVersion,
            Len(tblSoftware.softwareVersion) - CharIndex('.', tblSoftware.softwareVersion)
           )
    ELSE NULL  -- ELSE return nothing
  END AS versionB,

  CASE
    WHEN  -- IF there is a second decimal AND a third decimal
      CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1) > 0
      AND CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)+1) > 0
    THEN  -- THEN return what's between the second and third decimals
      Substring(tblSoftware.softwareVersion,
                CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1) +1,                                             -- next char after second decimal
                CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)+1)  -- third decimal position
                  - CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)                                             -- minus second decimal position
                  - 1                                                                                                                                      -- don't want the third decimal
               )
    WHEN  -- ELSE IF there is a second decimal AND no third decimal
      CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1) > 0
      AND CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)+1) = 0
    THEN  -- THEN return everything to the right of the second decimal
      Right(tblSoftware.softwareVersion,
            Len(tblSoftware.softwareVersion) - CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)
           )
    ELSE NULL  -- ELSE return nothing
  END AS versionC,

  CASE
    WHEN  -- IF there is a second decimal AND a third decimal
      CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1) > 0
      AND CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)+1) > 0
    THEN  -- THEN return everything to the right of the third decimal
      Right(tblSoftware.softwareVersion,
            Len(tblSoftware.softwareVersion) - CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion)+1)+1)
           )
    ELSE NULL  -- ELSE return nothing
  END AS versionD

  From
    tblSoftware
    Inner Join tblSoftwareUni On TblSoftwareUni.SoftID = tblSoftware.softID

Active Discussions

Lansweeper local admin users of a specific device
by  kdunnett   Go to last post Go to first unread
Last post: Today at 9:30:26 PM(UTC)
Lansweeper How to get total disk usage of all VM assets
by  Erik.T  
Go to last post Go to first unread
Last post: Today at 4:49:58 PM(UTC)
Lansweeper Windows Server 2016 & Patch Tuesday May 2020
by  Hendrik.VE   Go to last post Go to first unread
Last post: 5/22/2020 8:20:05 PM(UTC)
Lansweeper Users mapped "shared" printers
by  Andy.S  
Go to last post Go to first unread
Last post: 5/22/2020 4:16:23 PM(UTC)
Lansweeper Report on Assets in a Static Group
by  Andy.S   Go to last post Go to first unread
Last post: 5/22/2020 2:55:03 PM(UTC)
Lansweeper Windows 10 Activation
by  TruSynergy  
Go to last post Go to first unread
Last post: 5/21/2020 7:54:25 PM(UTC)
Lansweeper Filtering Report Based On Active Status
by  CyberCitizen   Go to last post Go to first unread
Last post: 5/21/2020 4:04:33 AM(UTC)
Lansweeper Windows: Unauthorized Administrators (Built-in)
by  Jackie.L  
Go to last post Go to first unread
Last post: 5/20/2020 8:01:17 PM(UTC)