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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 2 decimal places
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2revup
Posting Yak Master

112 Posts

Posted - 12/02/2013 :  17:54:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 12/02/2013 :  17:58:52  Show Profile  Reply with Quote
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

Edited by - TG on 12/02/2013 18:00:54
Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 12/02/2013 :  18:23:34  Show Profile  Reply with Quote
thats it I didnt do the convert but only the cast - thank you, thank you, thank you!!!!1
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 12/02/2013 :  23:19:36  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000