cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Martin_P
Engaged Sweeper
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
2 REPLIES 2
RCorbeil
Honored Sweeper II
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
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.)