Database Handicapping Software- JCapper

JCapper Message Board

          JCapper 101
                      -- Exporting and sorting data from past races for the horses in a race today

Home Register
Log In
By Exporting and sorting data from past races for the horses in a race today
jeff
10/15/2016
9:07:31 AM		From the email inbox:

--quote:
"When trying to mine data, I would like to look at situation where horses exit races with extreme Pacepressure situation.

Clearly there is no PacePressurelast field, but do you know the SQL expression that could query the situations where a horses last race was an extreme?

For that matter, for all factors where there is not a specific "XYZ"LAST factor or column in the database, how do you get a look back to the prior race?"
--end quote




--my reply:

I've created a few example sql expressions that can be run through the JCX File Exports Module in build 198 to create a .CSV file on your hard drive that enables you to look back at data from prior races.

These expressions use an INNER JOIN to pull fields from both the StarterHistory and StartersToday tables.



In layman's terms - here are the basics of how these expressions work:

As you probably know by now - when you build a database in Mode 3, 4, or 5 - JCapper populates the StarterHistory table.

What you may not (yet) know is that When you load .JCP files into the program and run a sql calc races - JCapper clears and then populates the StartersToday table with data that is almost identical to that found in the StarterHistory table. The primary difference being that the StartersToday table is for today's races and therefore does not contain results data.

However, after a sql Calc Races - the StartersToday table does contain almost all non results data for todays races that you will find in the StarterHistory table.

By using a JOIN statement - you can grab common fields like [date], track, race, horsename, etc from both tables.

For more info on JOINS - running the following phrases through Google will probably turn up plenty of web pages with examples that can be made to work in JCapper's JCX File Exports Module:

INNER JOIN - ACCESS
OUTER JOIN - ACCESS
JOINS - ACCESS - TSQL



Diving into the expressions themselves...

The part of each expression that reads "INNER JOIN STARTERSTODAY ON STARTERHISTORY.HORSENAME = STARTERSTODAY.HORSENAME" (without the quotes) causes the expression to pull data from both tables for those records where horsename is the same in both tables.

The part of each expression that reads "WHERE STARTERSTODAY.TRACK='BEL' AND STARTERSTODAY.RACE = 1" AND STARTERSTODAY.[DATE] = #10-13-2016#" (without the quotes) causes the expression to pull records for only those horses that appear in the StartersToday table for BEL R1 on 10-13-2016.

Note that I keyed those in manually.

To make expressions like these pull data for a different race - simply edit the [date] track race as needed.




The first expression is designed to display all past races in the StarterHistory table for each of the horses in the StartersToday table for BEL R1 on 10-13-2016 and uses "ORDER BY STARTERHISTORY.PACEPRESSURE DESC" (without the quotes) to sort the records by pacepressure in descending order.


SELECT STARTERHISTORY.[DATE], 
       STARTERHISTORY.TRACK, 
       STARTERHISTORY.RACE, 
       STARTERHISTORY.INTSURFACE, 
       STARTERHISTORY.DIST, 
       STARTERHISTORY.HORSENAME, 
       STARTERHISTORY.PACEPRESSURE,
       STARTERHISTORY.ODDS, 
       STARTERHISTORY.OFFICIALFINISHPOSITION, 
       STARTERHISTORY.WINPAYOFF, 
       STARTERHISTORY.PLACEPAYOFF,
       STARTERHISTORY.SHOWPAYOFF FROM STARTERHISTORY
       INNER JOIN STARTERSTODAY ON STARTERHISTORY.HORSENAME = STARTERSTODAY.HORSENAME
       WHERE STARTERSTODAY.TRACK='BEL'
       AND STARTERSTODAY.RACE = 1            
       AND STARTERSTODAY.[DATE] = #10-13-2016#           
       ORDER BY STARTERHISTORY.PACEPRESSURE DESC




The second expression is designed to display all past races in the StarterHistory table for each of the horses in the StartersToday table for BEL R1 on 10-13-2016 and uses "ORDER BY STARTERSTODAY.RAILPOSITION ASC, STARTERHISTORY.PACEPRESSURE DESC" (without the quotes) to sort the records first by horse order in the gate from the rail out - followed by pacepressure in descending order.


SELECT STARTERHISTORY.[DATE], 
       STARTERHISTORY.TRACK, 
       STARTERHISTORY.RACE, 
       STARTERHISTORY.INTSURFACE, 
       STARTERHISTORY.DIST, 
       STARTERHISTORY.HORSENAME, 
       STARTERHISTORY.PACEPRESSURE,
       STARTERHISTORY.ODDS, 
       STARTERHISTORY.OFFICIALFINISHPOSITION, 
       STARTERHISTORY.WINPAYOFF, 
       STARTERHISTORY.PLACEPAYOFF,
       STARTERHISTORY.SHOWPAYOFF FROM STARTERHISTORY
       INNER JOIN STARTERSTODAY ON STARTERHISTORY.HORSENAME = STARTERSTODAY.HORSENAME
       WHERE STARTERSTODAY.TRACK='BEL'
       AND STARTERSTODAY.RACE = 1            
       AND STARTERSTODAY.[DATE] = #10-13-2016#           
       ORDER BY STARTERSTODAY.RAILPOSITION ASC, STARTERHISTORY.PACEPRESSURE DESC





I'm thinking the third expression is probably closer to what you had in mind in your email.

This one is designed to display all past races in the StarterHistory table for each of the horses in the StartersToday table for BEL R1 on 10-13-2016 and uses "ORDER BY STARTERSTODAY.RAILPOSITION ASC, STARTERHISTORY.PACEPRESSURE DESC" (without the quotes) to sort the records first by horse order in the gate from the rail out - followed by pacepressure in descending order.


SELECT STARTERHISTORY.[DATE], 
       STARTERHISTORY.TRACK, 
       STARTERHISTORY.RACE, 
       STARTERHISTORY.INTSURFACE, 
       STARTERHISTORY.DIST, 
       STARTERHISTORY.HORSENAME, 
       STARTERHISTORY.PACEPRESSURE,
       STARTERHISTORY.ODDS, 
       STARTERHISTORY.OFFICIALFINISHPOSITION, 
       STARTERHISTORY.WINPAYOFF, 
       STARTERHISTORY.PLACEPAYOFF,
       STARTERHISTORY.SHOWPAYOFF FROM STARTERHISTORY
       INNER JOIN STARTERSTODAY ON STARTERHISTORY.HORSENAME = STARTERSTODAY.HORSENAME
       WHERE STARTERSTODAY.TRACK='BEL'
       AND STARTERSTODAY.RACE = 1            
       AND STARTERSTODAY.[DATE] = #10-13-2016#           
       ORDER BY STARTERSTODAY.RAILPOSITION ASC, STARTERHISTORY.PACEPRESSURE DESC




Link to a text file containing the above expressions so that you can copy and paste them into the Expression Tool in the JCX File Exports Module:
http://www.jcapper.com/messageboard/reports/JCXExpressionText.txt



JCX File Export Module BASIC OPERATING INSTRUCTIONS for Generating a .CSV File using a Custom SQL Expression:


  1. On the JCapper Main Module click the JCX File Exports button to launch the JCX File Exports Module.


  2. On the JCX File Exports Module, under File Extension, check the .CSV File box.


  3. On the JCX File Exports Module, click the Browse button to bring up a Dialog box. Use the Dialog box to navigate the folders on your hard drive. Choose a folder and either select or key the filename of the target .CSV file where you want your exported data written to. Then click the OPEN button on the dialog box to close the dialog box and make the interface paste the full path to your target .CSV file into the Output Filename box.

    Or alternately - skip the Browse button - and simply paste or key the full path of your target .CSV file into the Output Filename box.


  4. Check the Use Custom SQL Expression box to launch the SQL Expression Tool.


  5. On the SQL Expression Tool - paste or key a sql expression into the (big) SQL Expression box and hit the Apply button.

    Link to screenshot showing the third expression in the SQL Expression Tool:
    http://www.jcapper.com/messageboard/avatars/JCX_ThirdExpressionInCustomSQLTool.jpg

    The interface will validate your sql expression (test it for errors.) If it passes validation you will get an expression validated message. After clicking OK at the validation message the SQL Expression Tool will close.

    If the expression contains errors you will get an error message - and you will have to correct the expression before moving on to the next step.


  6. After the expression has been validated and the Expression Tools closes: Click the Generate Export button to run the export.

    The interface will execute your custom sql expression - and write the data returned by the results set to the .CSV file named in the Output Filename box.

    Link to screenshot showing the interface after generating the .CSV file:
    http://www.jcapper.com/messageboard/avatars/JCX_CSVFileGenerated.jpg



That's It!

From here you can double click the (just created) .CSV file to open it in Excel - where you can see the data.

Link to a screenshot of the .CSV file that was generated using the third expression (above) after opening it in Excel:
http://www.jcapper.com/messageboard/avatars/CSVFileInExcel.jpg

Note that VINO BLANCO had the highest PacePressure (most recent start) of any horse in the race.



Using an INNER JOIN between the StartersToday and StarterHistory tables - as presented in the examples above - is a strategy that can enable you to use the JCX File Exports Module to generate .CSV files that contain specific (targeted) data from the past races of the starters in any given race.

Feel free to edit the sample expressions as you see fit. (There's certainly nothing magic about pacepressure.)

The strategy presented above can be used for ANY of the data fields in the StarterHistory table.



-jp

.








~Edited by: jeff  on:  10/14/2016  at:  3:09:32 PM~

~Edited by: jeff  on:  10/15/2016  at:  9:07:31 AM~

Reply
jeff
11/4/2016
12:30:01 PM		I'm updating this thread with a build 198 progress update - because I think both of the items below apply here.



Progress Update 11-03-2016:

1. A new JCapper program download package (dated 11-03-2016) is now available on the JCapper Build 198 program downloads page.

Hint: Install this latest program update first - before diving in to the new features described in the new help doc mentioned below.

2. The new JCX File Exports Module Help Doc (last updated 11-04-2016) is now up on the Help Docs Page at JCapper.com.

Here's a direct link:
http://www.jcapper.com/helpDocs/JCXFileExportModule.html



The Help Doc itself is still being written. I'd say it's about 85% to 90% complete as I type this.

I'm posting about it here because I think it does a better job of explaining the Basic Operating Instruction steps than what's been previously posted (by me) in this thread.


-jp

.

Reply
Reply
Copyright © 2018 JCapper Software              back to the JCapper Message Board              www.JCapper.com