cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
forumuser1234
Engaged Sweeper
I have the following query and it works fine as is. However, I need to tweak the line that states "Convert(DECIMAL(3),Round((tblProcessor.NumberOfCores * tblAssets.NrProcessors) / 2, 0)) As ReportLicenseQty"

I need it so that if tblProcessor.NumberOfCores * tblAssets.NrProcessors / 2 is less than 2 it will just report 2. Otherwise, it will just report the calculation of tblProcessor.NumberOfCores * tblAssets.NrProcessors / 2. Is there an easy way to accomplish this? I need the output to be a whole number that is equal to 2 or greater and rounded up to the closest whole number (if it is 2.5 then it should be reported as 3).


Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblSqlServers.displayVersion As [SQL Edition],
tblSqlServers.skuName As [SQL License],
tblServicesUni.Caption As [SQL Service Caption],
tblServiceState.State As [SQL Service State],
Max(tblSqlServers.lastChanged) Over (Partition By tblAssets.AssetID)
As [SQL lastChanged],
tblAssets.Domain,
tblAssets.IPAddress,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssetCustom.Manufacturer,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.NrProcessors As Processors,
tblProcessor.NumberOfCores * tblAssets.NrProcessors As TotalCores,
Convert(DECIMAL(3),Round(tblAssets.Memory / 1024, 0)) As [Memory (GB)],
Convert(DECIMAL(3),Round((tblProcessor.NumberOfCores * tblAssets.NrProcessors)
/ 2, 0)) As ReportLicenseQty
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblServices On tblAssets.AssetID = tblServices.AssetID
Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
tblServices.ServiceuniqueID
Inner Join tblServiceState On tblServiceState.StateID = tblServices.StateID
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where tblSqlServers.skuName Not Like '%express edition%' And
tblSqlServers.skuName Not Like '%developer edition%' And
tblSqlServers.skuName Not Like '%internal database%' And
tblSqlServers.skuName Not Like '%enterprise evaluation edition%' And
tblServicesUni.Caption Like 'SQL Server (%' And tblServiceState.State =
'Running' And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
[SQL Edition]

1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
CASE
WHEN tblProcessor.NumberOfCores * tblAssets.NrProcessors / 2 < 2
THEN 2
ELSE tblProcessor.NumberOfCores * tblAssets.NrProcessors / 2
END

View solution in original post

2 REPLIES 2
forumuser1234
Engaged Sweeper
Thank you! This is exactly what I needed.
RCorbeil
Honored Sweeper II
CASE
WHEN tblProcessor.NumberOfCores * tblAssets.NrProcessors / 2 < 2
THEN 2
ELSE tblProcessor.NumberOfCores * tblAssets.NrProcessors / 2
END

New to Lansweeper?

Try Lansweeper For Free

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

Try Now