SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2revup
Yak Posting Veteran

97 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
5946 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
Yak Posting Veteran

97 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
5946 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000