CODEWITHSUNDEEP
sqlsql-server
choloboy
choloboy

Reputation: 815

Removing decimal to column using a function

I have a query that calls a function on a few of the columns. The results have 3 decimal places, its already being rounded but output always has "x.000".

Here is the query...

SELECT     SystemName, Caption, Label, 
           [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', Capacity) AS Capacity,
           [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', FreeSpace) AS [Free Space], 
           [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', Capacity - FreeSpace) AS [Used Space],
           100 * FreeSpace / Capacity AS [% of Free Space], 
           Warning = 
    CASE 
        WHEN 100 * FreeSpace / Capacity BETWEEN 10 AND 20 THEN 'Proactive Warning'
        WHEN 100 * FreeSpace / Capacity <= 10 THEN 'Critical Warning'
    END

FROM         CCS_Win32_Volume
WHERE     ((100 * FreeSpace / Capacity) < 20)
ORDER BY SystemName, Caption

Any input is appreciated! :)

Upvotes: 0

Views: 44

Answers (2)

tgolisch
tgolisch

Reputation: 6734

In your function, change the decimal accuracy of your return type. So, right now, I would expect the return type to be something like decimal(18,3). Which means 18 digits, three decimal places (to the right of the decimal). Instead, change your output to use the number of decimal places that you would prefer. For instance, if you would like zero, you could convert it to a bigint or decimal(18,0)

If you don't own the function, then you could cast the output after the function is called, but it doesn't look as nice

Cast([dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', Capacity) AS Decimal(18,0))

Upvotes: 1

Maurice Reeves
Maurice Reeves

Reputation: 1583

If you really don't care about maintaining that level of precision, you can use SUBSTRING and CHARINDEX to strip off the end:

SUBSTRING([dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', Capacity), 0, CHARINDEX([dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', Capacity), '.')

It might be easier if you store the results in a temp table and then apply your formatting, and you may need to cast to varchar first.

Good luck!

Upvotes: 1

Related Questions