|
JCapper Message Board JCapper 101
|
| Home |
Register
Log In |
| By | SQL expression for eliminating tracks by races run | |
| rosenowsr 8/22/2013 6:35:45 AM | When creating a universal UDM is there a way to have the key factors report not look at tracks that don't have a certain amount of races? Example: I want to create a UDM from my data base but only from tracks that have 500 races or more, without actually deleting these races, or writing each track code into the formula. I am sure there is a SQL expression for this.
| |
| jeff 8/23/2013 2:11:40 PM | There may be a way to do it entirely from within a sql expression. That said, I (as in me personally) haven't done it. The best I can offer is a work around that involves multiple steps. Step 1. - Determine how many races you have in the table for each track. To do this, you need to: A. Use a sql expression that returns 1 horse per race. For example: SELECT * FROM STARTERHISTORY WHERE RANKUPR = 1 The above sql expression uses UPR (but other factors in the program would also work.) FYI, I chose UPR because the value stored in the table will normally be a double precision number (carried out to several decimal places) such as 87.873214... Because the number generated by the program has several decimal places, the likelihood of 2 or more horses being tied for rank=1 in a race (although it does happen) is reduced va. using a factor where the numeric value is an integer... (for example 84 or 89 or 78.) FYI, the lower the likelihood of 2 or more horses being tied as the top ranked horse in the race: The greater the accuracy of your estimate for number of races in the table for each track. B. Break the data out BY TRACK. - Doing this is easy. Prior to executing the sql query, select BY TRACK from the factors drop down in the Data Window. When you execute a sql expression such as "SELECT * FROM STARTERHISTORY WHERE RANKUPR = 1" (without the quotes) with the data broken out BY TRACK, a glance at the results set will tell you how many races you have in the table for each track. Step 2. - Now that you can see how many races in the table there for for each track: Generate a list of acceptable track codes. For example: AQU-BEL-SAR-WOX, etc. Step 3. - Add your list of track codes to a UDM. For example: AND INSTR('AQU-BEL-SAR-WOX', TRACK) > 0 -jp .
|
| Reply |
|
Copyright © 2018 JCapper Software back to the JCapper Message Board www.JCapper.com |