I like it, just need to stop it at the right place e.g no of accas but all I wanted to see was how to join the CTE up with my tables. I remember at my last job converting all adjacency tables (ParentID,ID) into CTES for heirarchial code and I remember my techie manager telling me off. He said something like "Don't waste time learning new things, stick to what you know" - OMG he was such a Luddite. If only he had learned a few new tricks he wouldn't have wasted a year on a custom mail server he had made that was broken to bits.
By the way (apologies in advance) I've already got a Betfair API Key (worth £299 I think now) and an AutoBOT for placing bets e.g place accumulators do pretty well so does DUTCHING, automating the runners to go into those type of bets is pretty easy when you have 25 years+ of racing data (Runners, Races, lb claimed, wind surgery, head gear, stats on everything) today I won on the Scottish Grand National, and had the 2nd as a PLACE ONLY Bet. I'm sure if you look at all the questions I've asked on here over the years many probably have to do with betting.
I wrote my 1st Betfair BOT back in 2010, when it was a simple SOAP based API, 2 classes, and a resource XLS file for course abbreviations was all I needed. I made £1,500 testing it (as there was no "test mode" back then, and you had to use real money), with £2 min bets over the 1st year. Many a time I forgot an exit out of some loop and ended up placing 200 bets on one horse before I could stop it etc. In fact most of the code is actually Safety Measures to prevent things like:
- Spending over a % of your betting bank in a day.
- Ensuring that when you reach a profit of % of your bank you stop betting. No need to be greedy.
- Automatically raising and decreasing stakes as your bank grows or shrinks.
- Fully automated system creation due to all the Racing Data I have collected and still do automatically, to find profitable angles e.g Group Level horses who have a good OR but are now racing in Handicaps, lots of tables of Top Jockeys, Top Trainers (by win/place strike rates, last win, last place, last race), course bias for the few that do have draw bias, average winning times so I can find out the front runners that can be traded and a lot more.
- Automatic Trading, scalping the market in the last 5 mins as the price bubbles up and down as all the money hits it. Early Back to Lay trades that get matched both sides before the race, due to spotting favourites that are over priced and going to drop in price by race time.
And loads else. I specialise in automation, I used to have my own Racing Tips site, £100py or £5pwk to join, that had it's own race-cards, list of the Top Horses (rated by my own system), and over 550+ betting systems created automatically by the "BRAINIAC" (as I call it), that analyses all the data at night, finds niche systems, ensures they have made ROI over 10% in the last 30 & 100 days, and then offer up the tips.
LAY bets, PLACE ONLY, MULTI DAY WIN/PLACE ACCAS, DUTCHING, NAPS etc, automatic emails to members and people wanting "free tips" as a trial, a lovely looking website with areas that showed the next race's top market leaders, silks, price (WIN & PLACE markets), then below the latest results, a countdown time to the next race, loads of affiliates and automatic DB cleaning, re-indexing, management so I didn't have to do jack. I went on holiday to Iceland once and came back to a nice profit and the system hadn't failed once.
Whilst working I was renting out a VPS and was paying £90pm for hosting bandwidth which it needed - no probs whilst working, however Betfair kindly decided to change their API to a JSON based one, which meant a 2 class system in C# became a 25 class system. A lot more complex and it took at least a year to rewrite in my spare time and meant the website that was bringing in money on an automatic basis halted whilst I rewrote my Windows Service to use the API.
However once I had managed to rewrite it, I was just about to throw a lump into it to see how it would work and how much money I could make when....my company decided to outsource all their servers to OVH in France. Whilst the VPS was in the US, the Windows Service to connect to Betfair was running on a local server for quicker response times etc, and I had a little app on my PC that showed me the current balance, liability, money in use and a STOP/START button to control the service on my PC.
As I was grabbing data from all over the place including some horse racing sites I had build for a number of clients, and news that I posted to help with SEO, auto-tagging, using the tags as #hashtags for posting out to X no of Twitter accounts ( I think you can find my old WordPress based plugins I wrote myself due to the ones I found being so useless on an old site > https://www.strictly-software.com/plugins)
As I was basically getting free DB hosting, and piggy backing off a horse racing site our company had made, it meant my DB going off to OVH as well. I wasn't going to write such a complex DB in MySQL and put it on the VPS - I don't even know if MySQL can do Stored Procs with TRANSACTIONS, ROLLBACKS and returning multiple recordsets - didn't use to when I was working. May do now but the API interface we had was either PHP MyAdmin or Navicat, total BS compared to MS Management Console.
Then I got sick, lost my job and couldn't afford to keep the hosting up, as affiliates were killing off access to European countries by the day so that money dried up, plus people with all the AD Blocker tools etc didn't even see them. That was a big chunk of income lost and I got kicked of Bet365 and SkyBet for basically winning... sounds stupid but it's true if you are not a "Losing" customer you are of no value to a normal bookies. So when affiliates send people to those sites to sign up they get a % of how much they LOSE per month - nice!
So since being on the sick I have no server, two laptops, one "Borrowed" from work, and I have managed to install SQL Server Express 2017 on it and I am making a cut down version of my BOT to run from these laptops. Hopefully make enough money to rent another server, but as ££ sick income is not exactly great I make money on Betfair, Trading or Betting, and I basically have to use it to pay for things, I regularly have to turn £1 into a ton per week so I can withdraw £90 and spend it on things I need, then rinse n repeat.
However lately I have been getting into the football, especially EURO ELITE teams e.g Barcelona, Bayern Munich, PSG, Man City, Lazio, Ajax, Porto etc, and I have been doing accas, a 10p 20 fold acca won me £190. So I aim to make money by not staking much, and the idea of creating so many accas for little money e.g £2 @ 10p per acca = 20 accas, if I just get one right, I am in profit, as I stop at around £5-£6 return per acca which is enough to pay off the TOTAL STAKE across all accas and give me some profit. Obviously the more I win the more money I make and as the betting bank grows so will the stake.
I will hopefully use my regex n scraping skills to automate a lot of the params entered into the main stored proc that comes up with match outcomes but I obviously need to make it work manually before I can automate it all.
I am not a gambler, I am someone who makes money and spends it e.g my 50" UHD TV behind me was bought from winning the Grand National on Many Clouds Antepost at 66/1 in 2015 I think, I paid for a holiday in Iceland with a winning treble at the Breeders Cup, last horse Magician won at 8/1 - fav race of all time. Comes from the back on the home turn, wide (as he hates hitting other horses, literally goes backwards if he does), and Ryan Moore got him on the outside and the commentators didn't even notice him steam up the pack and beat he Fugue by a head. Iceland is expensive - never realised how much, and they drink more than Brits, so I was glad I had won enough to tick it off my bucket list.
So I don't want to end up sat in front of a high pay out slot machine like so many people do in betting shops, in fact I have never entered one since I won £140 on a treble, stopped in a car park that used to be free came back in 5 mins to find I was clamped as they had just changed it to charge people. I had to pay £125 out of that money to get unclamped so never again will I use a shop to bet. I just want to make more angles for making more money. I already won a 55.10 8 fold acca today on the football along with the racing so I am up for the day and never one to chase loses. Key to making money off betting is never to gamble, always bet if there is value, and never use a bookie that will never provide true odds for a horse, use an EXCHANGE like Betfair/Betdaq etc and TRADE. The football thing is new and hopefully I can get the games out without writing a procedural method to split the group up into smaller groups.
Your SQL has been very helpful, and I am sure many people would find it useful just not for betting, but as I said splitting a group of "things" up into random smaller groups of "things" in a SET BASED manner, I could have wrote a long stored proc to do it all with nested loops to spread the love etc but I wanted to see if there was a SET BASED approach to this problem.
Sorry for the life story.... but that's how it went and now goes....