→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎07-10-2020 03:27 AM
Solved! Go to Solution.
‎07-10-2020 05:30 PM
LEFT JOIN tblFileVersions ON tblFileVersions.AssetID=tblAssets.AssetID AND tblFileVersions.FilePathfull LIKE '%target.exe%'
WHERE
(tblFileVersions.FilePathfull IS NULL) -- 3a. target.exe doesn't exist
OR (tblFileVersions.FilePathfull LIKE '%target.exe%' -- 3b. target.exe exists
AND (tblFileVersions.FileVersion IS NULL -- but version is NULL
OR tblFileVersions.FileVersion = '' -- or blank
)
)
OR () -- 3c. the messy bit
OR (tblFileVersions.FilePathfull LIKE '%target.exe%' -- 3c. target.exe exists
AND (tblFileVersions.FileVersion < '8.8.8') -- and version is less than what we want
)
LEFT JOIN (Select
tblFileVersions.AssetID,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
CASE
WHEN -- IF there is a decimal
CharIndex('.', tblFileVersions.FileVersion) > 0
THEN -- THEN return everything to the left of it
Left(tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)-1)
ELSE -- ELSE just return the version
tblFileVersions.FileVersion
END AS versionA,
CASE
WHEN -- IF there is a second decimal
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
THEN -- THEN return what's between the first and second decimals
Substring(tblFileVersions.FileVersion,
CharIndex('.', tblFileVersions.FileVersion)+1, -- next char after first decimal
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) -- second decimal position
- CharIndex('.', tblFileVersions.FileVersion) -- minus first decimal position
- 1 -- don't want the second decimal
)
WHEN -- ELSE if there's a first decimal
CharIndex('.', tblFileVersions.FileVersion) > 0
THEN -- THEN return everything to the right of the first (only) decimal
Right(tblFileVersions.FileVersion,
Len(tblFileVersions.FileVersion) - CharIndex('.', tblFileVersions.FileVersion)
)
ELSE NULL -- ELSE return nothing
END AS versionB,
CASE
WHEN -- IF there is a second decimal AND a third decimal
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
AND CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1) > 0
THEN -- THEN return what's between the second and third decimals
Substring(tblFileVersions.FileVersion,
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) +1, -- next char after second decimal
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1) -- third decimal position
- CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+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('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
AND CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1) = 0
THEN -- THEN return everything to the right of the second decimal
Right(tblFileVersions.FileVersion,
Len(tblFileVersions.FileVersion) - CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)
)
ELSE NULL -- ELSE return nothing
END AS versionC,
CASE
WHEN -- IF there is a second decimal AND a third decimal
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
AND CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1) > 0
THEN -- THEN return everything to the right of the third decimal
Right(tblFileVersions.FileVersion,
Len(tblFileVersions.FileVersion) - CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1)
)
ELSE NULL -- ELSE return nothing
END AS versionD
From
tblFileVersions
Where
tblFileVersions.FilePathfull LIKE '%target.exe%') AS FileVersion ON FileVersion.AssetID=tblAssets.AssetID
. . .
WHERE
(FileVersion.FilePathfull IS NULL) -- 3a. target.exe doesn't exist
OR (FileVersion.FilePathfull LIKE '%target.exe%' -- 3b. target.exe exists
AND (FileVersion.FileVersion IS NULL -- but version is NULL
OR FileVersions.FileVersion = '' -- or blank
)
)
OR (FileVersion.FilePathfull LIKE '%target.exe%' -- 3c. target.exe exists
AND (FileVersion.FileVersion IS NOT NULL
AND FileVersion.FileVersion <> ''
AND Right('00000' + FileVersion.versionA, 5) + '.' -- pad out the pieces before comparing
+ Right('00000' + FileVersion.versionB, 5) + '.'
+ Right('00000' + FileVersion.versionC, 5) < '00008.00008.00008'
)
)
‎07-14-2020 03:50 PM
‎07-10-2020 05:30 PM
LEFT JOIN tblFileVersions ON tblFileVersions.AssetID=tblAssets.AssetID AND tblFileVersions.FilePathfull LIKE '%target.exe%'
WHERE
(tblFileVersions.FilePathfull IS NULL) -- 3a. target.exe doesn't exist
OR (tblFileVersions.FilePathfull LIKE '%target.exe%' -- 3b. target.exe exists
AND (tblFileVersions.FileVersion IS NULL -- but version is NULL
OR tblFileVersions.FileVersion = '' -- or blank
)
)
OR () -- 3c. the messy bit
OR (tblFileVersions.FilePathfull LIKE '%target.exe%' -- 3c. target.exe exists
AND (tblFileVersions.FileVersion < '8.8.8') -- and version is less than what we want
)
LEFT JOIN (Select
tblFileVersions.AssetID,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
CASE
WHEN -- IF there is a decimal
CharIndex('.', tblFileVersions.FileVersion) > 0
THEN -- THEN return everything to the left of it
Left(tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)-1)
ELSE -- ELSE just return the version
tblFileVersions.FileVersion
END AS versionA,
CASE
WHEN -- IF there is a second decimal
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
THEN -- THEN return what's between the first and second decimals
Substring(tblFileVersions.FileVersion,
CharIndex('.', tblFileVersions.FileVersion)+1, -- next char after first decimal
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) -- second decimal position
- CharIndex('.', tblFileVersions.FileVersion) -- minus first decimal position
- 1 -- don't want the second decimal
)
WHEN -- ELSE if there's a first decimal
CharIndex('.', tblFileVersions.FileVersion) > 0
THEN -- THEN return everything to the right of the first (only) decimal
Right(tblFileVersions.FileVersion,
Len(tblFileVersions.FileVersion) - CharIndex('.', tblFileVersions.FileVersion)
)
ELSE NULL -- ELSE return nothing
END AS versionB,
CASE
WHEN -- IF there is a second decimal AND a third decimal
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
AND CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1) > 0
THEN -- THEN return what's between the second and third decimals
Substring(tblFileVersions.FileVersion,
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) +1, -- next char after second decimal
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1) -- third decimal position
- CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+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('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
AND CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1) = 0
THEN -- THEN return everything to the right of the second decimal
Right(tblFileVersions.FileVersion,
Len(tblFileVersions.FileVersion) - CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)
)
ELSE NULL -- ELSE return nothing
END AS versionC,
CASE
WHEN -- IF there is a second decimal AND a third decimal
CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1) > 0
AND CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1) > 0
THEN -- THEN return everything to the right of the third decimal
Right(tblFileVersions.FileVersion,
Len(tblFileVersions.FileVersion) - CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)+1)+1)
)
ELSE NULL -- ELSE return nothing
END AS versionD
From
tblFileVersions
Where
tblFileVersions.FilePathfull LIKE '%target.exe%') AS FileVersion ON FileVersion.AssetID=tblAssets.AssetID
. . .
WHERE
(FileVersion.FilePathfull IS NULL) -- 3a. target.exe doesn't exist
OR (FileVersion.FilePathfull LIKE '%target.exe%' -- 3b. target.exe exists
AND (FileVersion.FileVersion IS NULL -- but version is NULL
OR FileVersions.FileVersion = '' -- or blank
)
)
OR (FileVersion.FilePathfull LIKE '%target.exe%' -- 3c. target.exe exists
AND (FileVersion.FileVersion IS NOT NULL
AND FileVersion.FileVersion <> ''
AND Right('00000' + FileVersion.versionA, 5) + '.' -- pad out the pieces before comparing
+ Right('00000' + FileVersion.versionB, 5) + '.'
+ Right('00000' + FileVersion.versionC, 5) < '00008.00008.00008'
)
)
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now