This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject

→ 🚀What's New? Explore Lansweeper's Fall 2024 Updates! Fall Launch Blog !

Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Report to compare softwareVersion

Martin_P
Engaged Sweeper

‎12-16-2019 04:50 PM

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
Labels:
0 Kudos
2 REPLIES 2
RCorbeil
Honored Sweeper II

‎01-22-2020 04:57 PM

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.
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
RCorbeil
Honored Sweeper II

‎01-22-2020 12:03 AM

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:
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)


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.)

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now