Move Decimal over - 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)

Move Decimal over

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-17 : 11:53:55

I would like to move my decimal two places to the left.

Example 24.495589856 becomes .249
The following is a portion of my code.

CAST(COUNT(CASE WHEN split IN ('8', '3') THEN answer_time ELSE NULL END) AS numeric(8, 2)) / ROUND(CAST(COUNT(CASE WHEN split = '17' THEN answer_time ELSE NULL END) AS decimal(6, 2)),2) AS 'Ratio'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 11:56:20

isnt it enough to divide by 100?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-17 : 11:59:30

I have a fraction function [dbo.udf_ConvertToFraction(.000 )] and I am tying to get this work with my function. I tried doing the code below and got an error message.

dbo.udf_ConvertToFraction( CAST(COUNT(CASE WHEN split IN ('8', '3') THEN answer_time ELSE NULL END) AS numeric(8, 2)) / ROUND(CAST(COUNT(CASE WHEN split = '17' THEN answer_time ELSE NULL END) AS decimal(6, 2)),2)/100)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 12:13:56

whats the error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-17 : 12:18:46

The conversion on the varchar value '6898559442' overflowed an int column. Maximum interger value exceeded. (Microsoft SQL Server, Error:248)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 12:22:02

thats obvious. its not because of /100 but its because one of data in your varchar column is over integer limit. change UDF parameter to longint type then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-17 : 12:26:08

can u explain further please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 12:51:50

check for the maximum value of answer_time field in your select first and see if you can accomodate it based on your expression logic

for example a value of '6898559442' cant be accomodated within datatype of numeric(8, 2) or decimal(6,2) as they wont be able to accomodate more than 6 and 4 digits in their integral part so change them to atleast (12,2)

also check whats the parameter datatype in UDF and make sure it also has sufficient size to accomodate the calculated expression value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-17 : 13:08:00

@ visakh16,

That makes since. I check the properties field for answer time and the datatyoe is datetime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 13:18:24

hmm? then whats point in making it numeric? can you illustrate with an example what you're trying to do?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-17 : 13:30:50

If I run this code below I get a Mixed fration:

dbo.udf_ConvertToFraction( CAST(COUNT(CASE WHEN split IN ('8', '3') THEN answer_time ELSE NULL END) AS numeric(8, 2)) / ROUND(CAST(COUNT(CASE WHEN split = '17' THEN answer_time ELSE NULL END) AS decimal(6, 2)),2))

But

if run the code below I get the error message.

dbo.udf_ConvertToFraction( CAST(COUNT(CASE WHEN split IN ('8', '3') THEN answer_time ELSE NULL END) AS numeric(8, 2)) / ROUND(CAST(COUNT(CASE WHEN split = '17' THEN answer_time ELSE

I do not want a mixed fraction

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-17 : 13:39:32

quote:
Originally posted by sergeant_time

If I run this code below I get a Mixed fration:

<snip>

I do not want a mixed fraction

We cannot read minds... What DO you want? And what are you trying to do. It seems like a rather bad solution, but it's hard to judge with the tiny amount of information provided.

Sample data and expected output is about the minimum for asking a database question. Here are some links that can help you prepare your question.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-17 : 13:59:06

@Lamprey and visakh16

I apologize for the confusion. I have a function that converts decimals into fractions. I will post my code in sections to better explain my intent.

1.) Complete queury

SELECT dbo.udf_ConvertToFraction('.495') AS Expr1, { fn MONTHNAME(queued_time) } AS MonthName, YEAR(queued_time)
AS Year, DATEPART(MM, queued_time) AS MonthOrder, MONTH(queued_time) AS Month,
CAST(COUNT(CASE WHEN split IN ('8', '3') THEN answer_time ELSE NULL END) AS numeric(8, 2))

/ ROUND(CAST(COUNT(CASE WHEN split = '17' THEN answer_time ELSE NULL END) AS decimal(6, 2)),2) AS 'Ratio'
FROM table
WHERE (queued_time >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)) AND (queued_time < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) AND
(split IN ('8', '17', '3'))
GROUP BY YEAR(queued_time), MONTH(queued_time), { fn MONTHNAME(queued_time) }, DATEPART(MM, queued_time)
ORDER BY Year, MonthOrder, Month, MonthName

Expr1 MonthName Year MonthOrder Month Ratio
99/200 July 2012 7 7 24.49558986

I have two goals that I would like to accomplish

GOAL 1

Ratio
24.49558986 (move Decimal over two places) = .24

GOAL TWO

Insert this code CAST(COUNT(CASE WHEN split IN ('8', '3') THEN answer_time ELSE NULL END) AS numeric(8, 2)) / ROUND(CAST(COUNT(CASE WHEN split = '17' THEN answer_time ELSE NULL END) AS decimal(6, 2)),2)

INTO THIS FUNCTION

dbo.udf_ConvertToFraction(CAST(COUNT(CASE WHEN split IN ('8', '3') THEN answer_time ELSE NULL END) AS numeric(8, 2))/ ROUND(CAST(COUNT(CASE WHEN split = '17' THEN answer_time ELSE NULL END) AS decimal(6, 2)),2)/100)

WITHOUT errors and to get a PROPER FRACTION ex: 6/25

PURPOSE: This fraction is a RATIO. So others For every 25 regular calls I get, there will be 6 escalted calls.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 14:04:05

you cant return fraction like that. it will still be in decimal only. whats the function udf_ConvertToFraction returnin? single value or resultset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-17 : 14:15:40

24 495589/1000000

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-17 : 14:17:28

By moving the decimal over you are reducing the fraction into its lowest form.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 14:20:24

quote:
Originally posted by sergeant_time

By moving the decimal over you are reducing the fraction into its lowest form.

ok..I was not commenting on that
what i suggested was you cant represent in X/Y format..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-17 : 14:26:22

Here is the link where I found the funtion.

http://www.techrepublic.com/blog/datacenter/convert-numbers-to-fractions-using-sql-server/348

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 14:41:32

thats what i told. its returning value as varchar and not as numeric.You wont be able to return them as fraction unless you convert it to string pattern

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-17 : 14:50:57

To make sure I am tracking. I would have to convert the column asnwer_time from datetime to a string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 15:44:10

quote:
Originally posted by sergeant_time

To make sure I am tracking. I would have to convert the column asnwer_time from datetime to a string.

for that whats the purpose of making it into a fraction?

what will be format of answer_time value and what is the output you're trying to make out of it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources