JCapper Message Board

JCapper 101
-- sql expression help - xthstartlayoff - sfshift - dist - distlastout

Home	Register Log In

sql expression help - xthstartlayoff - sfshift - dist - distlastout

jeff
3/26/2012
3:23:02 PM

--quote:

"hi jeff

got dedicated dbase for my developmental validation research projects set-up.

need help with 3 sql expressions:
1. xthstartoffoflayoff = 1,2,3 kept on bouncing and i played around with plurals&substituting race(s) for start(s)
2. surface switch: turf-to-dirt or dirt-to-turf
3. distance switches:
turnback defined (today's race 1 or more furlongs less than last race)
stretchouts defined (today's race 1 or more furlongs more than last race)

neither turnbacks nor stretchouts need to include sprint-to-route nor route-to-sprint qualifications (only furlongs)

thanks, henry"

--end quote

1. xthstartoffoflayoff = 1,2,3

Many ways to do this. Two are shown below. Note that placement of parenthesis characters in the 2nd line is critical to making it work:

AND XTHSTARTLAYOFF BETWEEN 1 AND 3

AND (XTHSTARTLAYOFF = 1 OR XTHSTARTLAYOFF = 2 OR XTHSTARTLAYOFF = 3)

2. surface switch: turf-to-dirt

For reference/background info, see the factor named surfaceshift at the supported factors page at JCapper.com:
http://www.jcapper.com/factorsglossary.asp

Turf to dirt (from the chart at the above link) is 4 through 7. The field name in the table schema is sfshift. Adding the following line to a sql expression will make the expression flag turf to dirt horses:

AND SFSHIFT BETWEEN 4 AND 7

Alternately, you could use something like the following 2 lines:

AND SFSHIFT >= 4
AND SFSHIFT <= 7

dirt-to-turf

Dirt to turf (from the chart at the above link) is 8 through 11. The field name in the table schema is sfshift. Adding the following line to a sql expression will make the expression flag dirt to turf horses:

AND SFSHIFT BETWEEN 8 AND 11

Alternately, you could use something like the following 2 lines:

AND SFSHIFT >= 8
AND SFSHIFT <= 11

turnback defined (today's race 1 or more furlongs less than last race)

The field names from the Table Schema Doc are dist and distlastout. Data recorded in both fields is race distance in yeards. Given that one furlong is 220 yards, the following lines added to a sql expression will do what you are asking:

AND STARTSLIFETIME > 0
AND DISTLASTOUT > 0
AND DIST > 0
AND DISTLASTOUT - DIST >= 220

Note that in the above lines I am using startslifetime > 0 and distlastout > 0 to make the sql expression avoid horses that do not have a previous running line to go back to.

Also note that because I am performing a calculation in the last line of the sql expression I have added and dist > 0 to ensure that there be an actual value in the dist field so as to prevent the expression from raising an error during a Calc Races.

stretchouts defined (today's race 1 or more furlongs more than last race)
The field names from the Table Schema Doc are dist and distlastout. Data recorded in both fields is race distance in yeards. Given that one furlong is 220 yards, the following lines added to a sql expression will do what you are asking:

AND STARTSLIFETIME > 0
AND DISTLASTOUT > 0
AND DIST > 0
AND DIST - DISTLASTOUT >= 220

Note that in the above lines I am using startslifetime > 0 and distlastout > 0 to make the sql expression avoid horses that do not have a previous running line to go back to.

Also note that because I am performing a calculation in the last line of the sql expression I have added and dist > 0 to ensure that there be an actual value in the dist field so as to prevent the expression from raising an error during a Calc Races.

-jp

.

jeff
3/29/2012
11:07:36 AM

--quote:

"Hi Jeff,

Q. Can you post a sample sql expression that flags horses going sprint to route?---And then route to sprint?

Thanks, KW"

--end quote

Before diving in to the actual sql expression, keep in mind that (at least in JCapper) routes are defined as races where race distance is greater than or equal to 1 mile. Sprints are defined as races where race distance is less than 1 mile. Also keep in mind that there are 1760 yards in a mile and that race distance recorded in the dist and distlastout fields of both the starterstoday and starterhistory tables is race distance stated in yards.

With the above in mind, here's how I would do sprint to route:

AND DISTLASTOUT > 0
AND DISTLASTOUT < 1760
AND DIST >= 1760

And here's how I would do route to sprint:

AND DISTLASTOUT >= 1760
AND DIST < 1760

Note that in the first expression I am requiring that distlastout be greater than 0. Adding this line to the expression prevents it from flagging 1st time starters - or horses without a prior start.

-jp

.

JCapper Message Board

JCapper 101 -- sql expression help - xthstartlayoff - sfshift - dist - distlastout

JCapper 101
-- sql expression help - xthstartlayoff - sfshift - dist - distlastout