AVG and number conversion troubles

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

AVG and number conversion troubles

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-19 : 17:39:32

I have a statement is my sp that converts minutes to hrs.

round(CAST(COALESCE(DURATION_MIN/60,0) as float),2) AS DURATION_HR
then

I have :
PIVOT (
AVG(DURATION_HR) for Month in ([1], [2], [3], [4], [5], [6], [7],

, [9], [10], [11], [12])

the output of this sp is looking like this:
Jan Feb Mar Apr May
1.85 1.24 0 0 0
2.69 0 2.425 0 0
0 0 0 0 0
0 0 9.69 0 0
0 0 0 1.8 0
1.45 0 0 0 0
0 0 0 1 0
0.44 0 0.333333333333333 3.73 0
0 0 0 0 0
0 0 0.51 0 0
0.32 0 0 6.18 0

I don't know why some of the values cannot be rounded up.... I would like to show 2 decimal places for 0.333333333333333 ex. 0.33 and "0" not 0.00 if the vaule is 0.

sample desired output would be:
0 0 0 1.8 0
0.44 0 0.33 3.73 0

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-20 : 02:57:49

Try this instead and see if it does you any good:

CONVERT(decimal(10, 2), AVG(DURATION_HR))

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-20 : 03:29:06

first, don't use float. use decimal data type. Float is approximate value.

if you want the duration in hour with decimal places, divide it by 60.0,


COALESCE(DURATION_MIN / 60.0, 0) AS DURATION_HR

If not, for 30 MINS, when convert to hour you will get 0. (try SELECT 30/60, 30/60.0 )

if you want the result in 2 decimal places, ROUND() it or convert it to decimal(10,2) for example


CONVERT(DECIMAL(10,2), COALESCE(DURATION_MIN / 60.0, 0)) AS DURATION_HR

Next,
AVG() will return you a value with lots of decimal behind. If you only wanted 2 decimal places, convert it at the SELECT of your query. You can't do it inside the PIVOT.


SELECT  CONVERT(DECIMAL(10,2), [1]), CONVERT(DECIMAL(10,2), [2]),  ...
FROM   ...
       PIVOT
       (
            AVG(DURATION_HR)
            FOR ...
       ) p

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-20 : 10:20:39

quote:
I would like to show 2 decimal places for 0.333333333333333 ex. 0.33 and "0" not 0.00 if the vaule is 0.

It sounds like the OP wants conditional rounding, which is really presentation issue. But, the only way I know how to do that would be with a CASE expression and converting the numeric data to strings.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-20 : 10:31:25

Do it on the front end ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources