JCapper Message Board

JCapper 101
-- gaps in SQL

Home	Register Log In

gaps in SQL

bettheoverlay
5/1/2012
10:29:20 AM

I was wondering if it were possible to create a sql expression that would tag UPR Rank 2 when GapUPR R2 < 5 and the gap between UPR R2 and UPR R3 is >= 10.

UPR R1 = 65
UPR R2 = 62
UPR R3 = 49

for example

jeff
5/2/2012
12:26:23 PM

The first part of this is easily doable using 2 lines:

AND RANKUPR = 2
AND GAPUPR < 5

That gets you all rank 2 UPR horses whose UPR values are within less than 5 points of the top UPR horse in the race.

The second part that you asked for is not doable in JCapper, at least not without additional programming.

I spent some time on it... mostly trying to figure out a way to write a sql expression that would:

1. Pull up the 2nd ranked UPR horse in the race from its record in the starterhistory table and persist UPR for that horse to a table created on the fly named AdditionalUPRValues - storing the UPR value for that horse in a data field (also created on the fly) named UPROfUPR2Horse.

2. Pull up the 3nd ranked UPR horse in the race from its starterhistory table record and persist UPR for that horse to a data field named UPROfUPR3Horse in the AdditionalUPRValues table (both created on the fly in the middle of the query.)

3. From there, pull up the UPROfUPR2Horse and UPROfUPR3Horse values from the AdditionalUPRValues table and apply the appropriate gap factor constraint like so:

AND UPROfUPR2Horse > 0
AND UPROfUPR3Horse > 0
AND UPROfUPR2Horse - UPROfUPR3Horse >= 10

Needless to say, the expression turned out to be complicated and I couldn't make it work in the Data Window - even though a little voice in the back of my head says I should have been able to make it work.

I'm thinking that a situation like this is best approached using an app running outside of JCapper.... It could certainly be done using Visual Basic, VBA code from inside of Access, or VBA code from inside of Excel.

-jp

.

~Edited by: jeff on: 5/2/2012 at: 12:26:23 PM~

bettheoverlay
5/2/2012
1:46:09 PM

Thanks, Jeff, for the effort.

JCapper Message Board

JCapper 101 -- gaps in SQL

JCapper 101
-- gaps in SQL