December 19, 2011 at 3:03 am
I run a number of horse racing sites and in a number of places I have to display the current price of a horse which in the UK is displayed in Fractions e.g 10/1 (put £1 on get £10 back plus your £1 stake).
Betfair however displays their prices in decimals e.g 2.00 equates to 1/1 (evens), 6/4 equates to 2.50 and so on.
I have a UDF that will convert fraction prices to decimal (which is below) but I am currently using a lookup table to do the inverse i.e convert decimal prices to fractions as I haven't come across a better way of doing it.
Does anyone have any code that would take a varchar(10) fraction price e.g 11/10, 1000/1 or 5/7 and return a decimal price?
Any help would be much appreciated.
My UDF for converting fractions to decimals is below
CREATE FUNCTION [dbo].[udf_CONVERT2DECIMAL]
(
@Odds varchar(10)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @a int,
@b-2 int,
@C decimal(10,6),
@DECIMAL decimal(10,2)
SELECT @Odds = COALESCE(@Odds,'')
-- sometimes the price checker site will show "evs" for 1/1 (evens) so save some time and return 2.00
IF @Odds = 'evs'
RETURN 2.00
-- check odds are in the correct format e.g 11/10 or 2/1 - shouldn't ever not be in this format due to front end parsing but just a safety check
IF @Odds NOT LIKE '%[0-9]/[0-9]%'
RETURN 0
SELECT@a = SUBSTRING(@Odds,0,CHARINDEX('/',@Odds))
,@B = SUBSTRING(@Odds,CHARINDEX('/',@Odds)+1,LEN(@Odds))
,@C = CAST(@A as decimal(10,6)) / @b-2
,@C = @C + 1
,@DECIMAL = ROUND(@C,2) -- round to 2 decimal places
RETURN @DECIMAL
END
Any help would be much appreciated and I thank anyone who gives any in advance.
Thanks
Rob
December 20, 2011 at 7:36 pm
This query will work out all odds at once:
DECLARE @Odds TABLE (
Odds varchar(20)
)
INSERT INTO @Odds (Odds)
SELECT '1/1'
UNION
SELECT '6/4'
UNION
SELECT '5,6/4'
UNION
SELECT '8/3'
UNION
SELECT '1/1'
UNION
SELECT '1,1'
UNION
SELECT '11/10'
UNION
SELECT '1000/1'
UNION
SELECT '5/7'
SELECT Odds,
CONVERT(DECIMAL(10,2), (CONVERT (decimal(18,6), A) + CONVERT (decimal(18,6), B) )/ CONVERT (decimal(18,6), B) ) GainRate
FROM (
SELECT Odds,
SUBSTRING(Odds,0,CHARINDEX('/',Odds)) A
,SUBSTRING(Odds,CHARINDEX('/',Odds)+1,LEN(Odds)) B
FROM @Odds
WHERE Odds LIKE '%/%' -- Split character must be present
) T1
WHERE A NOT LIKE '%[^0-9]%' AND B NOT LIKE '%[^0-9]%' -- split values must be integer numbers
You may wish to turn it into a view.
_____________
Code for TallyGenerator
December 21, 2011 at 2:33 am
Sorry, the question was wrong.
As you can see from the code I posted I already have code to convert a fraction price 6/4 to decimal 2.50
What I need is the inverse.
I need a UDF that will take a decimal price e.g 2.50 and convert it to a fraction price 6/4
The fraction to decimal part is easy it just the other way round I am having difficulty with.
Thanks for your response though.
December 21, 2011 at 8:01 am
maybe (just maybe):
create table #numbers ( a tinyint)
go
insert into #numbers (a)
(
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9
)
go
select * from #numbers
declare @percent decimal (10,2) = 2
select MIN (a)*@percent-MIN(a),MIN(a)
from #numbers
where floor(@percent * a) = @percent * a
drop table #numbers
Rob Reid-246754 (12/21/2011)
Sorry, the question was wrong.As you can see from the code I posted I already have code to convert a fraction price 6/4 to decimal 2.50
What I need is the inverse.
I need a UDF that will take a decimal price e.g 2.50 and convert it to a fraction price 6/4
The fraction to decimal part is easy it just the other way round I am having difficulty with.
Thanks for your response though.
December 21, 2011 at 4:50 pm
Rob Reid-246754 (12/21/2011)
I need a UDF that will take a decimal price e.g 2.50 and convert it to a fraction price 6/4
There 2 main problems here.
1) 2.50 may represent odds value 6/4 as well as 3/2 or 12/8.
Which one should be returned by the function?
2) Presentation of odds like 8/3 would be a rounded approximate decimal value.
It won't come back precisely to the original 8/3, so you're gonna need to figure out which division expression is the closest one to the current decimal price.
What I'd suggest you to do is to ditch functions and think database.
Database of knowledge.
Create a table with odds in one column and decimal price in another.
Populate it with all possible odds to be registered in your system and calculate decimal price for each of them.
Should not be more than 100k rows, which is nothing in DB terms.
Then you just know all the answers and simple SELECT can do the trick.
You're still gonna need to resolve the issue of "6/4" vs. "3/2".
_____________
Code for TallyGenerator
December 22, 2011 at 4:39 am
OK. Assuming you don't need to resolve 6/4 is the same as 3/2, this sort of, works:
feel free to add decimal places to get better precision if you have 1001/1000 bets :-):
create table #numbers ( a tinyint)
go
insert into #numbers (a)
(
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9
)
go
select * from #numbers
declare @percent decimal (10,6) = 2.666666
select cast(round(MIN (a)*@percent,0)as int)-MIN(a),MIN(a)
from #numbers
where round(@percent * a,0) between (0.99 * @percent * a) and (1.01 * @percent * a)
drop table #numbers
December 22, 2011 at 6:57 am
There are a number of special case odds where the fraction used is not the simplest (e.g. 6/4 and 100/30)
In order to cope with these you will need a database of "exceptions".
something like this should do (assumes that you have a "tally" table)
declare @percent decimal (10,6) = 1.667;
declare @special table (f varchar(25), odds varchar(25));
set nocount on;
insert into @special (f, odds) values ('10/3','100/30'),('3/2','6/4');
set nocount off;
selecta.[percent]
,IsNull(s.[odds], a.f) as [odds]
from(
select top 1 @percent as [percent]
,casewhen@percent <= 1.0
then'#error'
when@percent = 2.0
then'evens'
when@percent > 2.0
thencast(cast(round((@percent-1) * t.Number,0) as int) as varchar(10)) + '/' + cast(t.Number as varchar(10))
elsecast(t.Number as varchar(10)) + '/' + cast(cast(round((@percent-1) * t.Number,0) as int) as varchar(10)) + ' on'
end as f
fromtally t
wheret.Number > 0
andround(@percent * t.Number,0) = round(@percent * t.Number,2)
order byt.Number asc
) a
left join@special s
ons.f = a.f
;
December 22, 2011 at 8:42 am
I know about the problem with 2.50 being displayed as 6/4 and a myriad of other fractions e.g
6/4 2.50
9/6 2.50
12/8 2.50
15/10 2.50
18/12 2.50
21/14 2.50
24/16 2.50
27/18 2.50
3/2 2.50
30/20 2.50
33/22 2.50
36/24 2.50
39/26 2.50
42/28 2.50
45/30 2.50
48/32 2.50
51/34 2.50
54/36 2.50
57/38 2.50
60/40 2.50
63/42 2.50
66/44 2.50
69/46 2.50
72/48 2.50
75/50 2.50
This is why I am currently using a lookup table to get the price out.
However I know bookies always show specific prices for their odds so I obviously want the "right" fraction price and as a few people have alluded to the price 2.50 is ALWAYS shown as 6.4 and not 9/6. And the price 2.00 is evens not 10/10 or 100/100.
Therefore I need a way of distinguishing between the right and "wrong" fraction prices for a given decimal value.
At the moment as fraction prices have been scrapped from betting odds sites I have converted them into decimal prices and inserted them into the lookup table (if they don't already exist) but I think I should add a flag column which shows which fraction price to show per decimal price. Then I could get rid of a lot of the redundant data.
I have found a list of fractions that bookies use online > http://www.the-jag.com/a-guide-to-horse-racing/bookmakers/free-horse-racing-tips-guide-to-fractions-and-decimal-pricing/[/url]
Although not a complete list as it starts as 1.10 (and you get place prices of below that) I could take it, hack it and reformat it to help me out but it look like my lookup table is the only way to go. I just need to clean out all the non used fraction prices.
Thanks for all your help
Rob
December 22, 2011 at 2:37 pm
Rob Reid-246754 (12/22/2011)
Therefore I need a way of distinguishing between the right and "wrong" fraction prices for a given decimal value.
Just leave only "right" fraction in the lookup table, and you'll never get a "wrong" answer from it.
🙂
And I suggest to populate the table using fractions, not decimal prices, as the source of data.
Use your function to calculate decimal values.
This way you'll start from definitely correct answers, and you're not gonna need to figure out closest approximate.
Assuming that biggest numerator would be 999, and biggest denominator, say, 100 you'll get 100k fractions. After removing "wrong" fractions it will be even smaller.
And it's not gonna hold decimals which cannot be receive from any reasonable fraction.
With total size of the table about 2MB you may pin the table into memory and impress everyone with performance of your "function".
🙂
_____________
Code for TallyGenerator
December 23, 2011 at 3:21 am
I don't know if you were just slow posting or didn't read my previous comment but that was exactly what I proposed doing e.g using a lookup table and removing all the "incorrect" fractions.
The smallest decimal price I have in my table is 0 (for SP) and the largest is 1001 (1000/1).
I currently have 5195 records although I have many dupes e.g one decimal price matches multiple fractions.
I just need to obtain a correct (and full) list of fraction prices to filter out all the "non correct" ones I currently have.
Thanks for your help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply