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
Community FAQ
cancel
Turn on suggestions
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cancel
Turn on suggestions
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.)

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

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

Try Now
Top