JAMES
asked on
Convert fraction to decimal in a select statement
Hi,
We have a select statement which, amongst other things, returns a calculated value for a field using a complex-ish statement. In this statement we use a column called "Price" several times. We do this quite happily already but... the fractional part of "Price" may contain a percentage of the denomination (ie value could be 150.16 (16/32nd's) which actually means 150.5 in this instance etc).
Doing "normal" code to convert fractional based numbers to decimals is not the problem, the problem is how do we do this within the SELECT clause so that we only do it once and assign the value to a variable and not each time the "Price" column is used in the same equation?
I want to avoid calling a scaler function as this is too big an overhead on performance.
Any thoughts?
Thanks.
James.
We have a select statement which, amongst other things, returns a calculated value for a field using a complex-ish statement. In this statement we use a column called "Price" several times. We do this quite happily already but... the fractional part of "Price" may contain a percentage of the denomination (ie value could be 150.16 (16/32nd's) which actually means 150.5 in this instance etc).
Doing "normal" code to convert fractional based numbers to decimals is not the problem, the problem is how do we do this within the SELECT clause so that we only do it once and assign the value to a variable and not each time the "Price" column is used in the same equation?
I want to avoid calling a scaler function as this is too big an overhead on performance.
Any thoughts?
Thanks.
James.
Can you post a cut-down version of the table definition with some sample data?
ASKER
Hi,
Not sure how the table def. will help but here is a very cut down version of the Inline function. The "Price" column that comes from MyTable may be a decimal or it may be a fraction (as described in my opening post).
ALTER FUNCTION dbo.WorkSomethingOut
(
@Denomination int
)
RETURNS TABLE
AS
RETURN
(
SELECT dbo.MyTable.*, ((Price * Qty1) + (Price * Qty2)) as MyValue FROM dbo.MyTable
)
James.
Not sure how the table def. will help but here is a very cut down version of the Inline function. The "Price" column that comes from MyTable may be a decimal or it may be a fraction (as described in my opening post).
ALTER FUNCTION dbo.WorkSomethingOut
(
@Denomination int
)
RETURNS TABLE
AS
RETURN
(
SELECT dbo.MyTable.*, ((Price * Qty1) + (Price * Qty2)) as MyValue FROM dbo.MyTable
)
James.
>>(ie value could be 150.16 (16/32nd's) which actually means 150.5 in this instance etc). <<
could you elaborate this a bit ?
to avoid computing the price several times with a scalar udf,
maybe your best bet is to create a computed column in your table
eg
alter table mytable add price2 as floor(price)+(((price*100)
and then use the price2 column in your calculations
could you elaborate this a bit ?
to avoid computing the price several times with a scalar udf,
maybe your best bet is to create a computed column in your table
eg
alter table mytable add price2 as floor(price)+(((price*100)
and then use the price2 column in your calculations
ASKER
Hi Hilaire,
What I mean is that in some financial markets the price is sometimes quoted as a fraction but only the number after the decimal place.
eg.
150.16 = 150 is the integer and 16 is the fraction. So to convert the fraction part (ie. 16) we need to know the denomination which in this example is 32. As 16 is half of 32 this translates to a half. The integer (before the decimal place) always stays the same and we add the converted fraction to the integer; in our case 16 equates to 0.5 + 150 = 150.50.
I know it's mad but thats the way the trading markets publish US Government TBond prices!!
Interesting point about the computed column - looking into that now....
Thanks.
What I mean is that in some financial markets the price is sometimes quoted as a fraction but only the number after the decimal place.
eg.
150.16 = 150 is the integer and 16 is the fraction. So to convert the fraction part (ie. 16) we need to know the denomination which in this example is 32. As 16 is half of 32 this translates to a half. The integer (before the decimal place) always stays the same and we add the converted fraction to the integer; in our case 16 equates to 0.5 + 150 = 150.50.
I know it's mad but thats the way the trading markets publish US Government TBond prices!!
Interesting point about the computed column - looking into that now....
Thanks.
OK
I was wondering if the 32 was fixed or coming from another column.
I you think I can help for the computed column, please post the datatypes of the
price and denomination columns,
along with a few sample data for both
I was wondering if the 32 was fixed or coming from another column.
I you think I can help for the computed column, please post the datatypes of the
price and denomination columns,
along with a few sample data for both
ASKER
Hilaire,
Im just trying to figure out a) if computed columns are the answer and b) how to do them!
The problem with a) is that the price is a result of a join from a prices table so I'm trying to work out where to place the computed field.
Your thoughts would be very much appreciated - the columns are :-
Price = float
Denomination = integer (this is usually 32 or 64 but could be any value. We default it to 100 so that when the maths is done against a decimal value the result is the same).
Sample data is boring. Just make up the values but don't allow the fraction to go about the denomination value (our example = 32).
Many thanks again.
James.
Im just trying to figure out a) if computed columns are the answer and b) how to do them!
The problem with a) is that the price is a result of a join from a prices table so I'm trying to work out where to place the computed field.
Your thoughts would be very much appreciated - the columns are :-
Price = float
Denomination = integer (this is usually 32 or 64 but could be any value. We default it to 100 so that when the maths is done against a decimal value the result is the same).
Sample data is boring. Just make up the values but don't allow the fraction to go about the denomination value (our example = 32).
Many thanks again.
James.
If price and denomination are in different tables computed column is not an option I'm afraid
as for the sample data, in fact I was just wondering if 4/32nds
was stored as
XXX.04
or
XXX.4
as for the sample data, in fact I was just wondering if 4/32nds
was stored as
XXX.04
or
XXX.4
ASKER
Only price is stored in a different table. The denomination is stored in the same table - but I guess that does matter, it's still out!
4/32nds would be stored as
XXX.4
James.
4/32nds would be stored as
XXX.4
James.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are, of course, correct. It was my error.
2/32nds is expressed as XXX.02
and
20/32nds is expressed as XXX.20
.
.
.
etc
Sorry for confusion!
2/32nds is expressed as XXX.02
and
20/32nds is expressed as XXX.20
.
.
.
etc
Sorry for confusion!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Lowfatspread....
You're obviously older than I am ;-)
We have "nested" inline functions to join the tables where we perform either maths or joins and then call that from another inline function and so on.
...anyway the principle is still the same and I was already thinking about converting the value at a "lower" level.
Thanks.
You're obviously older than I am ;-)
We have "nested" inline functions to join the tables where we perform either maths or joins and then call that from another inline function and so on.
...anyway the principle is still the same and I was already thinking about converting the value at a "lower" level.
Thanks.
ASKER
Lowfatspread...
Question. I could add triggers to my tables that when any changes are made a calculation is done (in this example - fraction to decimal) and the new decimal value is stored in a new "CalculationPrice" column and the original value is stored in "DisplayPrice" column.
Would you think that method was preferable compared to converting each time I needed to work with the figures?
I know on the face of it the former choice would ultimately be more efficient when using the final results in calculations but there is the overhead of the triggers to think about especially when considering one of the tables receivess prices in real-time and allowing the trigger to do the calculation each time will be quite resource consuming.
Thanks.
James.
Question. I could add triggers to my tables that when any changes are made a calculation is done (in this example - fraction to decimal) and the new decimal value is stored in a new "CalculationPrice" column and the original value is stored in "DisplayPrice" column.
Would you think that method was preferable compared to converting each time I needed to work with the figures?
I know on the face of it the former choice would ultimately be more efficient when using the final results in calculations but there is the overhead of the triggers to think about especially when considering one of the tables receivess prices in real-time and allowing the trigger to do the calculation each time will be quite resource consuming.
Thanks.
James.
sorry yes I'd go with the trigger in this case , as i feel it will be more efficient overall...
the conversion will be done once only...
the conversion will be done once only...