January 5, 2023 at 7:48 pm
Hi,
I have not been on here for a while, I've been in hospital a lot recently and now I've got Cancer & MRSA which was a nice XMAS present, so I am on a lot of meds, Morphine, Fentanyl etc, so please pardon me for basics at the moment as my noggin is not screwed on real tight at the moment. Even though I can't work at the moment I still try to make use of my decades of SQL & Coding skills to make myself some money.
If any of you remember me, I am the bloke who has a big DB full of Horse Racing Data and bet to make money and spend it. Not waste it or chase losses or take loans out etc etc like a gambler, I am a bettor and I use my DB and stats to make money daily. I reguarly turn less than £1 into £100+ each week as I need that money to live on. I just won a nice 6/1 dead heat at Chelmsford actually.
I am running some queries on all my historical data to try and find some nice longshots since we had a 125/1 and then a 100/1 winner at Newcastle yesterday and I love a longshot, if it comes in! I have not bean able to beat my best priced winner which was the ARC win of 80/1 on Torquatto Tasso yet and I really want to.
However as they are longshots most will place (come in the 2-3/4/5 spots depending on the number of runners in the race) rather than win.
So instead of a win only bet it's best to do EachWay bets, so I am testing out queries on my data and I did write a SET based query for this but the CASE statements were so long winded on the page due to all the dividing of stakes, depending on win or placing, and then by the no of eachway payout positions, that I chose to write a UDF to make it more readable for me in my tests on old data. Which of course, backfitting is no way to test future race events but it does help to paper test future races without spending money to see if the system works or not.
However, I know people here, especially Jeff who has helped me before hates RBAR so I was wondering if there was a way to simplfy it and make it more readable in a SET based solution.
I have tested the logic out by going onto Betfair, picking a longshot, puting in the stake (that gets divided b 2, half for the win, half for the place positions), and seeing if the results match and they do, so it's just a case of making a CASE statement to be used in a SELECT that is not overly long winded and goes onto multiple lines as my one did.
The logic is all in the UDF and I hope you can understand what I am doing by passing in the required variables (columns in the RACES / RUNNERS tables) to the UDF instead of just using them in the SELECT in a long CASE statement.
Here is the code.
USE [Brainiac]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =====================================================================
-- Author:Rob Reid
-- Create date: 05-JAN-2023
-- Description:Gets the Eachway payout for a race with or without stake returned
-- Has to handle wins and just places with appropriate place dividends, the SET based
-- version just had too many long-winded CASE statements in it for it to be readable
/*
-- Tests, and checked against real runners on Betfair to see if my returns match
-- those on the online bookmaker's site when placing an EW bet.
-- Both are 7/1 EW bets or 8.00 in decimals, with the place part being 1/5th of the price for 3 places. So if the horse wins it will get both the win half at full price/odds and then the place half at 1/5th or 1/4th of the odds + the whole stake returned. If it just places (e.g 2-4 finish position for example), then only half the stake is used at 1/4th or 1/5th of the odds though these are stored as 0.25 or 0.20 in the DB plus just half the stake back, as only 1 half of the bet won (the place part).
-- winner, 1st finish place so both win+place parts returned with full stake
SELECT [dbo].[udf_GET_EACHWAY_RETURN](1,20.00,8.00,3,0.20,1) = £104.00 (correct)
-- placed only so only the place half is returned with just half of the stake
SELECT [dbo].[udf_GET_EACHWAY_RETURN](2,20.00,8.00,3,0.20,1) = £24.00 (correct)
*/
-- =====================================================================
ALTER FUNCTION [dbo].[udf_GET_EACHWAY_RETURN]
(
@FINISHPOSITION int, -- the place horse came in race
@Stake MONEY, -- the amount staked, it's divided in 2 so if it's a £10 EW bet we pass in £20.00, £10 for the winning half, £10 for the place half.
@DecimalOdds MONEY,-- the odds in decimals so we don't need to convert 7/1 to 8.00
@Eachwaypospayout int, -- already calculated on race input
@EachwayPrize MONEY,-- already calculated on race input
@ReturnStake BIT -- whether to return the stake or not
)
RETURNS MONEY
AS
BEGIN
DECLARE @Retval MONEY,
@Win MONEY,
@Place MONEY,
@Divider MONEY,
@RetStakeAmount MONEY
-- leave early if not won or in EW positions
IF @FINISHPOSITION > @Eachwaypospayout AND @Eachwaypospayout != 0
RETURN 0
-- save long case statements by working out place stake amount to be divided by now
-- no place £ if no EW places e.g a 2/3/4 horse race
IF @FINISHPOSITION > 1 AND @Eachwaypospayout = 0
RETURN 0
-- for winners or placers work out no to divide place half of stake by
ELSE IF @FINISHPOSITION >= 1 AND @Eachwaypospayout > 0 AND @FINISHPOSITION BETWEEN 1 AND @Eachwaypospayout
-- convert from 0.25 to 4.0 as there are 4 x 0.25 in 1 easier to divide by
SELECT @Divider = (1/@EachwayPrize)
ELSE
SELECT @Divider = 0 -- was not in the place positions
-- A win will be half the stake at the odds, and if EW places e.g > 5 runners
-- then half the stake at either 1/4,1/5 etc of the odds however it's stored
-- as a decimal e.g 0.25 or 0.20 so we converted to a whole number earlier
IF @FINISHPOSITION = 1
BEGIN
SELECT @Win = (@Stake/2)*(@DecimalOdds-1),
@Place = CASE WHEN @Eachwaypospayout = 0 THEN 0 ELSE (@Stake/2)*((@DecimalOdds-1)/@Divider) END,
@RetStakeAmount = CASE WHEN @ReturnStake=1 THEN @Stake ELSE 0 END
END
ELSE IF @FINISHPOSITION BETWEEN 2 AND @Eachwaypospayout
BEGIN
SELECT @Win = 0,
@Place = CASE WHEN @Eachwaypospayout = 0 THEN 0 ELSE (@Stake/2)*((@DecimalOdds-1)/@Divider) END,
@RetStakeAmount = CASE WHEN @ReturnStake=1 THEN @Stake/2 ELSE 0 END
END
ELSE -- sanity check - blame the Morphine!
BEGIN
SELECT @Win = 0,
@Place = 0,
@RetStakeAmount = 0
END
-- join the two parts of an EW bet up, the win + place winnings + stake
SELECT @Retval = @Win + @Place + @RetStakeAmount
RETURN @Retval
END
Thanks for your help in advance.
Rob
January 5, 2023 at 9:43 pm
It not a fully asked question imo. It's a good one tho. Hopefully some of that medication was prescribed. You're looking for more efficient code. It comes down to demonstrating the equivalency of the new function. Imo it requires a representative set of the 6 inputs with with which to establish equivalency. Looking at the parameter names and descriptions... uggh it's a bunch of lingo. For example the parameter @ReturnStake is BIT so there needs to be 2 values for testing. What about all the others?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 5, 2023 at 10:47 pm
I can make a couple of recommendations:
The sanity check isn't really necessary - if you validate the inputs before calling the function. And by validating the inputs - put those checks in the outer queries where clause. Convert this to an inline-table valued function and use CROSS APPLY in the outer query to call it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2023 at 2:34 am
Anyway code could be inferred from what's there. I don't want to know about horse racing. Untested you could try
drop FUNCTION [dbo].[udf_GET_EACHWAY_RETURN_itvf];
go
create or ALTER FUNCTION [dbo].[udf_GET_EACHWAY_RETURN_itvf](
@FINISHPOSITION int, -- the place horse came in race
@Stake MONEY, -- the amount staked, it's divided in 2 so if it's a £10 EW bet we pass in £20.00, £10 for the winning half, £10 for the place half.
@DecimalOdds MONEY,-- the odds in decimals so we don't need to convert 7/1 to 8.00
@Eachwaypospayout int, -- already calculated on race input
@EachwayPrize MONEY,-- already calculated on race input
@ReturnStake BIT -- whether to return the stake or not
)
RETURNS table with schemabinding AS return
select /* win */ iif(@Eachwaypospayout > 0 AND @FINISHPOSITION=1, (@Stake/2)*(@DecimalOdds-1), 0) +
/*place*/ iif(@Eachwaypospayout > 0 AND @FINISHPOSITION BETWEEN 2 AND @Eachwaypospayout, (@Stake/2)*((@DecimalOdds-1)/(1/@EachwayPrize)), 0) +
/*RetStakeAmount*/ iif(@ReturnStake=1, @Stake, 0) as retval
where not(@FINISHPOSITION > @Eachwaypospayout AND @Eachwaypospayout != 0)
-- leave early if not won or in EW positions
and not(@FINISHPOSITION > 1 AND @Eachwaypospayout = 0);
-- no place £ if no EW places e.g a 2/3/4 horse race
go
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 6, 2023 at 2:53 am
After looking at it again (and again) it was updated a few times
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 6, 2023 at 3:31 am
Thanks Steve,
I cannot remember if you are the person who set this site up or not, I know he was called Steve, just forgot his surname. I used to love coming to this site when I was working as a coder and remember having a massive debate with Ming the Merciless, I cannot remember his real name but I had 2 SQL Expert books he had made, with all the ways of doing hierarchal queries using boundaries rather than adjacency tables which were the fad back in the day and stack UDFs before CTEs even were thought off.
Of course all my medication is prescribed, I am in the UK, so the NHS is paying for my noggin to be off key at the moment and keep the pain away as I slowly rot inside due to delayed operations and strikes by nurses and ambulance staff and surgeons who literally insinuated I should cut my own cancer out my leg. I went for an op on the 28th Dec and he refused due to the MRSA I caught in hospital, so I said "so do you expect me to get a scalpel and dig this thing out myself" and all he said was "well its not illegal" (twice), not "No you might cut an artery or hurt yourself", just that "it wasn't illegal". I have nurses and chemists all telling me I should sue the NHS as I've been badly let down by them and I have. I just don't want to sue until at least someone either attempts to cut this out of me or definitively says they are not going to, which is basically a death sentence really. Anyway I hate thinking about all that, thats why betting and morphine help take my mind off it all.
I didn't put as many test cases in as I am more interesting in the betting and getting exactly the same results as if I filled in a betslip online, which this UDF does.
I am sorry about the Horse Racing lingo but obviously I name variables after columns that represent the parts that make up a bet in a race.
So an explanation of all the parameters would be:
@FINISHPOSITION int, -- the finish position that the horse came in the race. It could come 1st, 2nd or 12th. This is why I put all the logic into the UDF rather than using CASE statements as when I wrote it out as just a SELECT with CASE statements there was so much math and divisions, and branches to ignore losers, handle winners with 2 parts to calculate or a placed horse with 1 part to calculate with the appropriate positions, payout amounts and so on which made the SQL look so long and complicated I just wanted a UDF with all relevant parameters passed in and a result returned whether it was £0 or £100 or £25 - it just made it much easier to look at and read knowing I was getting the right return amount back which was all handled in the UDF.
@Stake MONEY, -- this is obviously the TOTAL amount staked. With an Eachway bet if you put down £5 and then tick the Eachway box you are staking £10. £5 for it to win at the full odds e.g 7/1 or 8.0, and £5 for it to place which is controlled by how many runners there are racing, the place positions and the divider amount as you only get a quarter or a fifth of the odds at your stake for the place part. So that is why on input I save the no of place spots and the amount of the odds you multiply this part of the stake by e.g 0.25 or 0.20 this is why I convert it into a whole number in the UDF so 0.20 becomes 5 and 0.25 becomes 4.
@DecimalOdds MONEY,-- the odds in decimals so we don't need to convert 7/1 to 8.00 each time as I do that on input or a change to the price. Its a a column against the horse racing that can be directly passed in.
@Eachwaypospayout int, -- This is the position you must come within to achieve a place payout. If you come 1st you obviously get half your stake at full odds/price, and half at either 0.25/0.20 of the price plus your full stake back on top. If you only come 3rd and the Eachwaypospayout is 3 places then you would get half of your stake * 0.20/0.25 etc plus half your stake back on top. If you come above this no e.g 4th place you get nothing, nada returned to your wallet.
@EachwayPrize MONEY,--this is the size of the price/odds a place position is multiplied by e.g 0.25/0.20 (its stored like that), which is why in the UDF I convert it to either 4 or 5 which is the divider. So for any place part of the bet that wins half your stake is multiplied by a quarter or a fifth (0.20/0.25) of the total price e.g 8.00 so betting £10 EW, half is for the win, half is for the place so if calculating the place part its £5 * (8.00 / 0.25) + £5 (if you want to return your half of the stake back as well)
@ReturnStake BIT -- this is either yes or no and tells the UDF whether I want to know the profit or the return amount. So if I won the race and passed in TRUE I would get my winnings PLUS my full stake back. If I placed but just wanted to know my profit I would pass in FALSE and get back only the place calculation (£5 * (8.00 / 0.25)) etc. Sometimes I need to know Return amount other times just profit so this flag helps determine whether the full, half or none of my stake is returned on top of any profit if there is any.
I hope that helps. I did try a SELECT but with all these variables the CASE statement was so long with repeating parts e.g for calculating the place part for a WIN and for just a PLACE plus whether to add on the retuned stake or not etc and it just looked like it had became a long horrible equation from GCSE Maths class, whereas a UDF which handles all the logic inside and parameters/columns are just passed in makes it so much more readable when running the tests.
I don't know if that helped you or answered any of your questions or not but I am setting up my bets for later today at the moment and didn't expect any responses so soon. I forgot how good this site was for getting good SQL gurus to help!
January 6, 2023 at 3:36 am
I didn't realise we were both writing responses at the same time, so I misses your post about not wanting to know about "horse racing" so sorry for informing you - hey you might be so good at Maths that with some good SQL and historical data you can make yourself a 2nd tax free income over in the UK if you wanted.
Anyway thanks for the answers I see you have edited them a few times so I don't know if you are finished or not, but will check it out in the morning as its 3.30 am here in the UK and I have the doctors in the morning for a check up.
Thanks for the help.
January 6, 2023 at 4:46 am
Sorry I shouldn't have wrote that. I love horses and racing too. Yes, it passes my visual test now 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 6, 2023 at 3:39 pm
I cannot remember if you are the person who set this site up or not, I know he was called Steve, just forgot his surname.
That's Steve Jones. He's the site creator and moderator, ringleader, ... A lot of people probably don't realize how much functionality is built into SSC because they just read the articles etc. Anyone can submit an article or other content and see if maybe it works out. It's a "make a pull request" type set up. People can talk whatever if they wish to submit content here's how: in the upper righthand corner of the site click next to the pencil icon where it says "Write for us" and then click the "Contribute Article" button. That launches the content publishing portal. Lots of different forms with a lot of functionality. Overall it's based on WordPress/project nami but with custom features
I used to love coming to this site when I was working as a coder and remember having a massive debate with Ming the Merciless, I cannot remember his real name but I had 2 SQL Expert books he had made, with all the ways of doing hierarchal queries using boundaries rather than adjacency tables which were the fad back in the day and stack UDFs before CTEs even were thought off.
Ha there is a scary bookwriting person around here! Ha I'll let the other members guess as the screenname. In my run in with Ming he told me he was going to go around making money fixing my mistakes! Ha he's gonna have to hustle to make that work out in his favor
Of course all my medication is prescribed, I am in the UK, so the NHS is paying for my noggin to be off key at the moment and keep the pain away as I slowly rot inside due to delayed operations and strikes by nurses and ambulance staff and surgeons who literally insinuated I should cut my own cancer out my leg. I went for an op on the 28th Dec and he refused due to the MRSA I caught in hospital, so I said "so do you expect me to get a scalpel and dig this thing out myself" and all he said was "well its not illegal" (twice), not "No you might cut an artery or hurt yourself", just that "it wasn't illegal". I have nurses and chemists all telling me I should sue the NHS as I've been badly let down by them and I have. I just don't want to sue until at least someone either attempts to cut this out of me or definitively says they are not going to, which is basically a death sentence really. Anyway I hate thinking about all that, thats why betting and morphine help take my mind off it all.
Idk I'm sorry to hear of your medical issues. For sure health systems everywhere could improve
I didn't put as many test cases in as I am more interesting in the betting and getting exactly the same results as if I filled in a betslip online, which this UDF does.
- If the horse won, which would be half the stake on the win * odds, and the other half on the place part (if there are place positions as you don't get them in the UK unless there are over 5 horses running - and I saw a one horse race the other day, don't even know why it went ahead but the odds were so good for it just not to fall over going as slow as it wanted I couldn't believe it). The place part is the other half of the stake * either a 1/4 or 1/5 or sometimes 1/3 of the odds. These odds are usually fractional in the UK e.g 7/1 but I convert them to decimal on input so 7/1 becomes 8.0 etc.
- If the horse only came 2nd and there were 3 place spots you would just get half your stake amount (so if you staked £10 in total, £5 goes for the win half, and £5 for the place half), and that is multiplied by either 1/4th or 1/5th of the price (so 8 / (4 or 5)) , however these are stored in the DB as decimals 0.25 o 0.20 which is why I convert them to divisible numbers first. Plus you would get half your stake returned as well for that placing part, whilst for a win you get your full stake returned.
I am sorry about the Horse Racing lingo but obviously I name variables after columns that represent the parts that make up a bet in a race.
So an explanation of all the parameters would be:
@FINISHPOSITION int, -- the finish position that the horse came in the race. It could come 1st, 2nd or 12th. This is why I put all the logic into the UDF rather than using CASE statements as when I wrote it out as just a SELECT with CASE statements there was so much math and divisions, and branches to ignore losers, handle winners with 2 parts to calculate or a placed horse with 1 part to calculate with the appropriate positions, payout amounts and so on which made the SQL look so long and complicated I just wanted a UDF with all relevant parameters passed in and a result returned whether it was £0 or £100 or £25 - it just made it much easier to look at and read knowing I was getting the right return amount back which was all handled in the UDF.
@Stake MONEY, -- this is obviously the TOTAL amount staked. With an Eachway bet if you put down £5 and then tick the Eachway box you are staking £10. £5 for it to win at the full odds e.g 7/1 or 8.0, and £5 for it to place which is controlled by how many runners there are racing, the place positions and the divider amount as you only get a quarter or a fifth of the odds at your stake for the place part. So that is why on input I save the no of place spots and the amount of the odds you multiply this part of the stake by e.g 0.25 or 0.20 this is why I convert it into a whole number in the UDF so 0.20 becomes 5 and 0.25 becomes 4.
@DecimalOdds MONEY,-- the odds in decimals so we don't need to convert 7/1 to 8.00 each time as I do that on input or a change to the price. Its a a column against the horse racing that can be directly passed in.
@Eachwaypospayout int, -- This is the position you must come within to achieve a place payout. If you come 1st you obviously get half your stake at full odds/price, and half at either 0.25/0.20 of the price plus your full stake back on top. If you only come 3rd and the Eachwaypospayout is 3 places then you would get half of your stake * 0.20/0.25 etc plus half your stake back on top. If you come above this no e.g 4th place you get nothing, nada returned to your wallet.
@EachwayPrize MONEY,--this is the size of the price/odds a place position is multiplied by e.g 0.25/0.20 (its stored like that), which is why in the UDF I convert it to either 4 or 5 which is the divider. So for any place part of the bet that wins half your stake is multiplied by a quarter or a fifth (0.20/0.25) of the total price e.g 8.00 so betting £10 EW, half is for the win, half is for the place so if calculating the place part its £5 * (8.00 / 0.25) + £5 (if you want to return your half of the stake back as well)
@ReturnStake BIT -- this is either yes or no and tells the UDF whether I want to know the profit or the return amount. So if I won the race and passed in TRUE I would get my winnings PLUS my full stake back. If I placed but just wanted to know my profit I would pass in FALSE and get back only the place calculation (£5 * (8.00 / 0.25)) etc. Sometimes I need to know Return amount other times just profit so this flag helps determine whether the full, half or none of my stake is returned on top of any profit if there is any.
I hope that helps. I did try a SELECT but with all these variables the CASE statement was so long with repeating parts e.g for calculating the place part for a WIN and for just a PLACE plus whether to add on the retuned stake or not etc and it just looked like it had became a long horrible equation from GCSE Maths class, whereas a UDF which handles all the logic inside and parameters/columns are just passed in makes it so much more readable when running the tests.
I don't know if that helped you or answered any of your questions or not but I am setting up my bets for later today at the moment and didn't expect any responses so soon. I forgot how good this site was for getting good SQL gurus to help!
Good then I'll assume my code works :). Unless you've got a test set up and can prove it doesn't
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 10, 2023 at 12:55 am
Hi Seve
Sorry I haven't gotten back to you but I had a lot of hospital and doctor appointments lately, so much, I haven't even had a chance to place any bets until today. I have split his post into two as I was writing so much about the other things you mentioned about his site, articles, and MING. So the final UDF is in the next post!
Thanks for the concern about my health it has been a real annoyance plus pure pain which means I cannot work anymore which is why I make my money from betting, tax-free as well, so I need to get these functions correct to ensure when I use them they will return the correct amounts.
So you have had run-ins with Ming as well then? I remember some big work my boss wanted, a DB system that was basically easy to set up e.g the non-techies would fill a form out, choose which DB settings, categories, layout to copy from which they could then edit, and automatically set the site up in IIS, with special folder permissions, ISAPI files automatically built, and it to be as quick and run fast as possible. I debated him for ages, as I chose a mix of relational DB for the part all sites used, and my own version of an EAV for the custom parts.
He said I should be writing a new database for each site I needed to build, which would take 1000 more times than I had to develop. He also said I should be using natural keys instead of a surrogate, and I was just "Nope, my system works fine, is fast to set up, runs fast, and I don't have to find natural keys where they don't exist when there is nothing wrong with a surrogate PK/FK for joining. I'd rather join on an integer than 4 columns that make the row unique."
I had loads of people privately message me at the time saying "thank you" as they all seemed to think he was a bit of a bully and no-one stood up to him on these boards. I read his books about hierarchal design using boundaries and it seemed so much work to keep them updated or created when there were much easier ways, might have been fast to do lookups, but edits I don't know as I never tried one LOL.
However, as a Universal Maser of Evil, with the bald head and beard, he obviously was a relational DB purist and would hate all the NoSQL DBs that are around nowadays. He must type really fast as if I could create 500+ websites that used my one DB which had automatically generated tables and indexes that were unique to each website within and it only took a non-techie to fill out a step-by-step form process to create a demo site and then do the colouring in (as I call the CSS/Styling of a site), then he must have a lot of time to code in between writing his books.
I just had to keep my boss happy, and when they are not technical it is very hard to convince him to go down one path which takes much more time than a path that is quick and makes the company money very quickly due to the fast turnaround from a client wanting a site and me enabling a demo version to be seen by them online with a few forms for URL, Site Name, DB to copy settings and categories from, which paid mine and other employees wages. So that was the basis of my dealings with MING about 15+ odd years ago.
Anyway, how did you turn from "no waning to know about horse racing" to "loving horse racing" so fast? Did you run some stats and find some systems that would make you a ton of tax-free money or something?
I never realised his site was based on WordPress, I've had a few of my own, and made some plugins after delving into the code and seeing how bad some of it was, such as having a function call to return the site's ISAPI URL on each loop iteration. I made a Sitemap plugin of m own that had 7 DB lookups compared to the market leader which had 7000+ depending on the no of posts, as well as an Autotag plugin that used RegEx to find Acronyms, Names, Company Names, etc with no 3rd party lookups, linked to another Twitter plugin that allowed you to have content analysis and send multiple tweets to multiple accounts using the tags as #hashtags etc. I remember speaking to a developer and he said the core code needed a full rebuild but as it was built by volunteers it had got so big it was unfeasible to rewrite from scratch which is why most of their updates are front-end, layout-type updates.
I think I did submit an article to this site years ago, can't remember if it was published but I did a company meeting on it for the techies at work. It was based on query plans and forced parameters and the speed differences with Adhoc queries compared to parameterized & stored procs I think, I can see an old article I wrote on an old blog post from 2009 that might have been the basis for it > http://blog.strictly-software.com/2009/06/optimizing-query-with-forced.html. A lot of the developers were web 1st and saw a DB as just somewhere to plunk data and used convoluted SQL in the front end for all CRUD leaving holes for SQL injection etc. I might have written it and not submitted I'm not sure, so long ago now, but I have always loved his site.
So, the answer is in the next post, thanks for all the help!
Rob
January 10, 2023 at 1:09 am
Hi,
This is the answer and code.
Here is the final version, a couple of issues I had to resolve which were both missing from my initial code requirements and errors in the version you wrote.
I don't know the speed difference between writing the UDF as an IF ELSE logic procedural script as compared to one SELECT statement, but hopefully, you can see now why when I had it outside a UDF it was so unreadable due to all the logic branches.
Also as I am no on a server and using SQL Server Express I don't have the performance analyser tool, or if I do I cannot find it!
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =====================================================================
-- Author:Rob Reid
-- Create date: 05-JAN-2023
-- Description:Gets the Eachway payout for a race with or without stake returned
-- Has to handle wins, losses and just placed runners with appropriate place dividends and
-- return the correct result with or without the stake returned depending on the result
/*
-- Tests, and checked against real runners on Betfair to see if my returns match
-- those on the online bookmakers site when placing an EW bet
-- Win/Place/Loss tests are 7/1 EW bets or 8.00 in decimals, with the place part being 1/5th of the price for 3 places
-- winner, 1st place so both win+place parts returned with full stake
SELECT [dbo].[udf_GET_EACHWAY_RETURN](1,20.00,8.00,3,0.20,1) = £104.00 (correct)
-- placed only so only the place half returned with half of the stake
SELECT [dbo].[udf_GET_EACHWAY_RETURN](2,20.00,8.00,3,0.20,1) = £24.00 (correct)
-- loss
SELECT [dbo].[udf_GET_EACHWAY_RETURN](8,20.00,8.00,3,0.20,1) = -£20.00 (correct)
-- Call Params
SELECT [dbo].[udf_GET_EACHWAY_RETURN](FINISHPOSITION,@Stake,DecimalOdds,Eachwaypospayout,EachwayPrize,1)
*/
-- =====================================================================
CREATE FUNCTION [dbo].[udf_GET_EACHWAY_RETURN]
(
@FinishPosition int, -- the place horse came in race
@Stake MONEY, -- the amount staked, it's divided in 2 so if a £10 EW bet pass in £20.00
@DecimalOdds MONEY,-- the odds in decimals so we don't need to convert 7/1 to 8.00
@Eachwaypospayout int, -- already calculated on race input
@EachwayPrize MONEY,-- already calculated on race input
@ReturnStake BIT -- whether to return the stake or not
)
RETURNS MONEY
AS
BEGIN
DECLARE @Retval MONEY
SELECT @Retval =
/* loss so return the lost stake amount bet on the race*/
iif(@FinishPosition > @Eachwaypospayout AND @Eachwaypospayout != 0,(@Stake * -1),0) + -- a loss of stake
/* won so won half + place half */
iif(@Eachwaypospayout > 0 AND @FinishPosition=1, (@Stake/2)*(@DecimalOdds-1), 0) +
/* placed, so only half the stake * 1/4th or 1/5th of odds */
iif(@Eachwaypospayout > 0 AND @FinishPosition BETWEEN 1 AND @Eachwaypospayout, (@Stake/2)*((@DecimalOdds-1)/(1/@EachwayPrize)), 0) +
/* Return right stake, we only return £ if runner won or placed NOT if it lost */
/* Won the race so the full stake is returned */
iif(@ReturnStake=1 AND @FinishPosition=1, @Stake, 0) +
/* Placed in race so only half of the stake is returned */
iif(@ReturnStake=1 AND @FinishPosition BETWEEN 2 AND @Eachwaypospayout, @Stake/2, 0)
WHERE -- leave early if the runner only placed and there were no EW positions
-- e.g a 1/2/3 horse race on Betfair who does have 2 places for a 4 horse race
NOT(@FinishPosition > 1 AND @Eachwaypospayout = 0);
RETURN @Retval
END
Thanks for all your help with this code, and if you have found a betting system that makes £££, give us a DM 🙂
Rob
January 12, 2023 at 2:09 am
Sorry I haven't gotten back to you but I had a lot of hospital and doctor appointments lately, so much, I haven't even had a chance to place any bets until today. I have split his post into two as I was writing so much about the other things you mentioned about his site, articles, and MING. So the final UDF is in the next post!
Thanks for the concern about my health it has been a real annoyance plus pure pain which means I cannot work anymore which is why I make my money from betting, tax-free as well, so I need to get these functions correct to ensure when I use them they will return the correct amounts.
It's for a good cause. Around where I live they have betting on harness racing which attracts shady characters and seems prone to, um, unexpected results
So you have had run-ins with Ming as well then? I remember some big work my boss wanted, a DB system that was basically easy to set up e.g the non-techies would fill a form out, choose which DB settings, categories, layout to copy from which they could then edit, and automatically set the site up in IIS, with special folder permissions, ISAPI files automatically built, and it to be as quick and run fast as possible. I debated him for ages, as I chose a mix of relational DB for the part all sites used, and my own version of an EAV for the custom parts.
He said I should be writing a new database for each site I needed to build, which would take 1000 more times than I had to develop. He also said I should be using natural keys instead of a surrogate, and I was just "Nope, my system works fine, is fast to set up, runs fast, and I don't have to find natural keys where they don't exist when there is nothing wrong with a surrogate PK/FK for joining. I'd rather join on an integer than 4 columns that make the row unique."
I had loads of people privately message me at the time saying "thank you" as they all seemed to think he was a bit of a bully and no-one stood up to him on these boards. I read his books about hierarchal design using boundaries and it seemed so much work to keep them updated or created when there were much easier ways, might have been fast to do lookups, but edits I don't know as I never tried one LOL.
However, as a Universal Maser of Evil, with the bald head and beard, he obviously was a relational DB purist and would hate all the NoSQL DBs that are around nowadays. He must type really fast as if I could create 500+ websites that used my one DB which had automatically generated tables and indexes that were unique to each website within and it only took a non-techie to fill out a step-by-step form process to create a demo site and then do the colouring in (as I call the CSS/Styling of a site), then he must have a lot of time to code in between writing his books.
I just had to keep my boss happy, and when they are not technical it is very hard to convince him to go down one path which takes much more time than a path that is quick and makes the company money very quickly due to the fast turnaround from a client wanting a site and me enabling a demo version to be seen by them online with a few forms for URL, Site Name, DB to copy settings and categories from, which paid mine and other employees wages. So that was the basis of my dealings with MING about 15+ odd years ago.
Regarding Ming he threw the same word salad my way and I stood up for myself. Idk if it's productive to argue or what. My response is to try to answer questions well. Fwiw what you've written makes a lot of sense. As far as keys go my thinking has evolved recently based on some of what Jeff has presented about lately and others. Using guids and splitting the cluster key from the integer primary key seems to make sense in certain cases.
Regarding your system it sounds impressive. Complete systems are still a challenge to provide "as a service". Whenever someone has a working system it's worthy of respect. The more parts integrated the more it's a challenge at least initially.
I never realised his site was based on WordPress, I've had a few of my own, and made some plugins after delving into the code and seeing how bad some of it was, such as having a function call to return the site's ISAPI URL on each loop iteration. I made a Sitemap plugin of m own that had 7 DB lookups compared to the market leader which had 7000+ depending on the no of posts, as well as an Autotag plugin that used RegEx to find Acronyms, Names, Company Names, etc with no 3rd party lookups, linked to another Twitter plugin that allowed you to have content analysis and send multiple tweets to multiple accounts using the tags as #hashtags etc. I remember speaking to a developer and he said the core code needed a full rebuild but as it was built by volunteers it had got so big it was unfeasible to rewrite from scratch which is why most of their updates are front-end, layout-type updates.
I think I did submit an article to this site years ago, can't remember if it was published but I did a company meeting on it for the techies at work. It was based on query plans and forced parameters and the speed differences with Adhoc queries compared to parameterized & stored procs I think, I can see an old article I wrote on an old blog post from 2009 that might have been the basis for it > http://blog.strictly-software.com/2009/06/optimizing-query-with-forced.html. A lot of the developers were web 1st and saw a DB as just somewhere to plunk data and used convoluted SQL in the front end for all CRUD leaving holes for SQL injection etc. I might have written it and not submitted I'm not sure, so long ago now, but I have always loved his site.
So, the answer is in the next post, thanks for all the help!
Rob
Yeah WordPress might seem "unfeasible to rewrite from scratch" but there are already big pieces of the puzzle which could be combined. Imo there wasn't a proper separation of concerns from the beginning. The HP in PHP stands for "hypertext preprocessor". Data access and error handing is included in the preprocessing along with looping through html tags, etc. Also, it appears the data types used would seem to necessitate escaping text in code. At the end of the day tho it largely works and it's a proven solution. What would a modern solution look like? In UI components all of the major vendors (like Telerik, DevExpress, Syncfusion, etc.) have way more features and are JSON ready. In data access and backend integrations PHP has to compete with Microsoft and .NET. They lose that one already too. It's a question of offering the combined elements as a cohesive system and having great applications built
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 14, 2023 at 1:42 am
Hi,
Just an update, I was noticing I was getting unexpected results such as £0 returned for a winner but in races where there was no EW available e.g a 3-horse race so no EW pay out spots.
Also, I was getting NULLS in some places due to the finish position being 0 and not a 1-30 figure. Sometimes the system stores the finish position as 0, especially if the jockey fell, was disqualified, unseated, or for a myriad of other reasons. Also sometimes on form cards as it is impossible to tell if 011P means that the horse, lost and then won twice before being pulled up or lost and then came 11th, cards usually stick to 0 if the place position was over 10 to make the form easier to read.
Therefore I had to modify the UDF slightly, taking away the WHERE clause altogether as it was not needed plus the logic within it had to be handled above. Also, I had to handle where a winner won in a race with 0 place positions which meant it was a straight-out win.
Not too many changes but was fiddly and required more test cases which I have put above the UDF in the comments.
USE [Brainiac]
GO
/****** Object: UserDefinedFunction [dbo].[udf_GET_EACHWAY_RETURN] Script Date: 14/02/2023 00:23:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =====================================================================
-- Author: Rob Reid
-- Create date: 05-JAN-2023
-- Description: Gets the Eachway payout for a race with or without stake returned
-- Has to handle wins, losses and just places with appropriate place dividends and
-- return the correct result with or wihout he stake returned depending on the result
/*
-- Tests, and checked against real runners on Betfair to see if my returns match
-- those on the online bookmakers site when placing an EW bet
-- Win/Place/Loss tests are 7/1 EW bets or 8.00 in decimals, with the place part being 1/5th of the price for 3 places
-- winner, 1st place so both win+place parts returned with full stake
SELECT [dbo].[udf_GET_EACHWAY_RETURN](1,20.00,8.00,3,0.20,1) = £104.00 (correct)
-- winner, race has no payout positions for places though so cannot be an EW race just a win only race
SELECT [dbo].[udf_GET_EACHWAY_RETURN](1,20.00,8.00,0,0,1) = £140.00 (correct)
-- placed only so only the place profit half returned with half of the stake
SELECT [dbo].[udf_GET_EACHWAY_RETURN](2,20.00,8.00,3,0.20,1) = £24.00 (correct)
-- placed only but its not an EW race so a total loss
SELECT [dbo].[udf_GET_EACHWAY_RETURN](2,20.00,8.00,0,0,1) = -£20.00 (correct)
-- loss where runner came 8th over the 3 each way position payout spot
SELECT [dbo].[udf_GET_EACHWAY_RETURN](8,20.00,8.00,3,0.20,1) = -£20.00 (correct)
-- loss where runner came over 10th or fell/unseated jockey so it is stored in the system as 0
SELECT [dbo].[udf_GET_EACHWAY_RETURN](0,20.00,8.00,0,0,1) = -£20.00 (correct)
-- Call Params
SELECT [dbo].[udf_GET_EACHWAY_RETURN](FINISHPOSITION,@Stake,DecimalOdds,Eachwaypospayout,EachwayPrize,1)
*/
-- =====================================================================
ALTER FUNCTION [dbo].[udf_GET_EACHWAY_RETURN]
(
@FinishPosition int, -- the place horse came in race
@Stake MONEY, -- the amount staked, its divided in 2 so if a £10 EW bet pass in £20.00
@DecimalOdds MONEY,-- thhe odds in decimals so we dont need to convert 7/1 to 8.00
@Eachwaypospayout int, -- already calculated on race input
@EachwayPrize MONEY,-- already calculated on race input
@ReturnStake BIT -- whether to return the stake or not
)
RETURNS MONEY
AS
BEGIN
DECLARE @Retval MONEY
SELECT @Retval =
/* loss so return the lost stake amount bet on the race*/
iif((@FinishPosition > @Eachwaypospayout OR @FinishPosition = 0),(@Stake * -1),0) + -- a loss of stake
/* won in a race that was not an EW race as no EW places e.g a 3 runner race so a normal win */
iif(@FinishPosition = 1 AND @Eachwaypospayout = 0, (@Stake * (@DecimalOdds-1)), 0) +
/* won so won half + place half */
iif(@Eachwaypospayout > 0 AND @FinishPosition=1, (@Stake/2)*(@DecimalOdds-1), 0) +
/* placed, so only half the stake * 1/4th or 1/5th of odds */
iif(@Eachwaypospayout > 0 AND @FinishPosition BETWEEN 1 AND @Eachwaypospayout, (@Stake/2)*((@DecimalOdds-1)/(1/@EachwayPrize)), 0) +
/* Return right stake, we only return £ if runner won or placed NOT if it lost */
/* Won the race so the full stake is returned */
iif(@ReturnStake=1 AND @FinishPosition=1, @Stake, 0) +
/* Placed in race so only half of the stake is returned */
iif(@ReturnStake=1 AND @FinishPosition BETWEEN 2 AND @Eachwaypospayout, @Stake/2, 0)
RETURN @Retval
END
Also, I love a good argument sometimes, especially with a bully, it can be very satisfying just knowing you are winding them up 🙂
Rob - Still Alive - And still waiting for a consultation to find out what's going on!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply