JCapper Message Board

JCapper 101
-- Math??

Home	Register Log In

Math??

rosenowsr
6/21/2012
12:03:28 PM

Is it possible to do math with sgl? Such as showing stretch gain or stretch loss?If so can you show an example.

Am I allowed 2 questions? LOL

When I use the 'or' statement I have to use parentheses to get the correct calculations. Example ((rankf18<=2) or (rankf22<=2)). Is this normal?

jeff
6/21/2012
1:31:14 PM

Q. Can you do math in your sql expressions?

A. Absolutely!

Stretch gain:

• The relevant data fields in the table are:
lenstrcall
posstrcall
lenfincall
posfincall

• Before attempting to write out individual lines, it pays to 'step back' and consider the problem in a big picture kind of way (much like one of my calculus teachers tried to get us to do freshman year of college.)

There are multiple cases involving stretch gain. (The trick is to handle each case.)

Case 1. Horses that have a valid last race running line. (For example, you don't want to be attempting math involving beaten lengths and positions of first time starters or horses with 'broken' running lines.)

The following lines should get the job done:

AND (POSSTRCALL > 0
AND POSFINCALL > 0)

Case 2. Horses that did not have the lead at either the stretch call or the finish call.

Here, after trapping to make sure position at the streth and finish calls are both greater than 1, we can apply some simple math:

AND ((POSSTRCALL > 1
AND POSFINCALL > 1
AND (LENSTRCALL - LENFINCALL) > 0)

Case 3. Horses that may not have gained in terms of beaten lengths from the stretch to the finish call in the most recent running line - but DID gain in positional call. Depending on your own preferences (or what you see in the Data Window) you may or may not want to include this particular case. But for purposes of handling all possible cases of stretch gain, I will include this case here:

OR (POSSTRCALL - POSFINCALL > 0)

Case 4. Horses that did not have the lead at the stretch call but did have the lead at the finish call.

Here, we can avoid doing math altogether and get by with a simple evaluation of position at the stretch and finish calls:

OR (POSSTRCALL > 1
AND POSFINCALL = 1))

Putting it all together - Those appear to be the cases where horses can have a stretch gain. From here, it's just a matter of assembling the above lines.

The final expression looks something like this:

AND ((POSSTRCALL > 0
AND POSFINCALL > 0)

AND ((POSSTRCALL > 1
AND POSFINCALL > 1
AND (LENSTRCALL - LENFINCALL) > 0)

OR (POSSTRCALL - POSFINCALL > 0)

OR (POSSTRCALL > 1
AND POSFINCALL = 1)))

Note that I've added parenthesis characters at the beginning (immediately after the first occurrence of the word AND) and to the very end of the above lines, making it behave as a single expression.

Also note that I've added parenthesis characters to wrap each of the OR expressions - to make them behave as single expressions.

And, to answer your specific question about parenthesis characters... The answer is yes. And the way that you used them in your example is correct.

Also note that I've purposely left a space between the lines for each case - hopefully - making the entire thing (somewhat) easy to read.

-jp

.

~Edited by: jeff on: 6/21/2012 at: 1:31:14 PM~

rosenowsr
6/21/2012
3:16:17 PM

Calculus 101--WOW. Thanks for the lesson. This SQL is great stuff. Even if I don't understand most of it. LOL

endearus
7/13/2012
11:52:30 AM

Is there a way to RANK the field for lengths gained in the stretch (or any other interaction among variables)?

Thanks

Eric

jeff
7/13/2012
12:59:52 PM

Rank for lengths gained/lost in the stretch?

No.

It's something I did look at (back in 2001-2002) when I was doing my initial R&D when deciding what to include/what not to include in the database.

At the time, I didn't see enough bang for the buck roi-wise. (Rank and gap for late pace based numbers were more telling.)

-jp

.

JCapper Message Board

JCapper 101 -- Math??

JCapper 101
-- Math??