Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Displaying Fewer Decimal Places Expand / Collapse
Author
Message
Posted Thursday, January 8, 2009 7:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, Visits: 251
I have a query that is compiling hundreds of rows of data to determine performance statistics. While the calculations are correct, they are displaying far more decimal places than is necessary (15 places).

Is there a way to limit all display output to a fixed number of decimals (say 4 or 5) with one command?

Or for each column in my select list would I have to use the CAST() function?

Post #632398
Posted Thursday, January 8, 2009 8:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:26 AM
Points: 10,381, Visits: 13,436
What are you using to display the results? What are the datatypes of the columns in the calculations? Can you post the query?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #632447
Posted Thursday, January 8, 2009 8:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, Visits: 251
Here is a simplified version of the query. The actual query contains about a dozen items in the select statement similar to this calculation.

SELECT
Param1
, Param2
, Param3
, EXP(SUM(CASE WHEN(Date BETWEEN '2007-01-01' AND '2008-01-01') THEN (LOG(1+Gains)) ELSE 0 END))-1 as CY2007PercGain

FROM myTable

GROUP BY
Param1
, Param2
, Param3

Param1, Param2, Param3, and Gains are all type DECIMAL(18,8), and Date is type DATETIME
Post #632465
Posted Thursday, January 8, 2009 8:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, Visits: 251
Oh yeah, I am just running this query in the MS SQL Server Mgmt Studio if that makes a difference.
Post #632467
Posted Thursday, January 8, 2009 8:45 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
investigate the ROUND() function.

~BOT


Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #632471
Posted Thursday, January 8, 2009 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:26 AM
Points: 10,381, Visits: 13,436
I think using cast or convert is your only option if you are just using SSMS. It is using whatever the data type is for your column, so you could reset that if you don't need that precision, unless you are using float.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #632472
Posted Thursday, January 8, 2009 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:26 AM
Points: 10,381, Visits: 13,436
Oh yeah, forgot about ROUND()



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #632473
Posted Thursday, January 8, 2009 9:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, Visits: 251
I looked into the ROUND() function as you guys suggested. Maybe my implementation is off because while it rounds the value, it leaves on all the trailing zeros after the rounding takes place.

And since the goal is to make this reader friendly, the trailing zeros aren't helping.
Any ideas how to get rid of these trailing zeros?


DECLARE @Gains AS DECIMAL(18,8)
SET @Gains=RAND()

SELECT
@Gains
, ROUND(@Gains, 4)

Gave me:
0.67144808 0.67140000
Post #632539
Posted Thursday, January 8, 2009 10:27 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
Aha! Decimal must be immune to a true round::

Try this one!
DECLARE @Gains AS float
SET @Gains=RAND()

SELECT
@Gains
, ROUND(@Gains, 4)


so maybe in your code you could cast to a float and round it.

~BOT


Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #632620
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse