2 decimal places - SQL Server Forums

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)

2 decimal places

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2013-12-02 : 17:54:10

this is doing my head in I have tried round, casting to decimals nothing is working

Query is:

Select (sum(tot_talk_time) / 3600)* 1.00 / COUNT(distinct [Agent_login]) as ChatTime_SiteAvg
from [PhoneandChat]
left join Agents on Agent_login=agent_login_id
WHERE TOT_TALK_TIME > 0 and phone_skill LIKE '%Chat%'
and Agent_login = 'xxxxxxx'
and Location = 'xxx'

As I said I have tried casting the above with (10,2) and a round nothing seems to work I am getting a result that looks like

with a cast
169.647222

with a round:
169.650000

Any help would be AWESOME!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-02 : 17:58:52

cast to decimal(10,2) should work - and it will round as well. Did you try this?

Select convert(decimal(10,2), (sum(tot_talk_time) / 3600)* 1.00 / COUNT(distinct [Agent_login])) as ChatTime_SiteAvg
from [PhoneandChat]
left join Agents on Agent_login=agent_login_id
WHERE TOT_TALK_TIME > 0 and phone_skill LIKE '%Chat%'
and Agent_login = 'xxxxxxx'
and Location = 'xxx'

EDIT:
you never actually said what your desired results look like.
Like this?


select convert(decimal(10,2), 169.647222)

OUTPUT:
169.65

Be One with the Optimizer
TG

2revup
Posting Yak Master

112 Posts

Posted - 2013-12-02 : 18:23:34

thats it I didnt do the convert but only the cast - thank you, thank you, thank you!!!!1

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-02 : 23:19:36

you're welcome!
But just so you know - CAST and CONVERT do the same thing:


select convert(decimal(10,2), 169.647222)
select cast(169.647222 as decimal(10,2))

OUTPUT:
169.65
169.65

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources