JCapper Message Board

JCapper 101
-- Tips on using SQL from a SQL newbie

Home	Register Log In

Tips on using SQL from a SQL newbie

GlennF
4/28/2010
1:33:25 PM

As an SQL newbie I was pleasantly surprised how easy it was to get the general "hang" of using SQL but in experimenting with it I've found out there are a number of things which were not immediately obvious which greatly increase the power of using SQL to develop UDM's. Here are some things I discovered.

Testing going forward:

AND DATE>DATEADD("D",-365,NOW())
AND not DATE>DATEADD("D",-21,NOW())

this returns results from the past year excluding the past 3 weeks. To test going forward
remove the "not" and it tests just the past three weeks. (Does anyone know what kind of qualifier can go in the double parentheses following NOW?)

Using factors as variables:

for example rankJPR<= fieldsize/3 returns the top third of the field in JPR rank

One or more of a series of factors

and ((rankf01=1)+(rankf02=1)+(rankf03=1)+ (rankf04=1)+ (rankf05=1) + (rankf06=1))<0

is true if any one of the factors is true

and ((rankf01=1)+(rankf02=1)+(rankf03=1)+ (rankf04=1)+ (rankf05=1) + (rankf06=1))<-1

is true if any two of the factors are true etc. ( an expression evaluates to -1 if it is true)

Does anyone have any additional tips?

~Edited by: GlennF on: 4/28/2010 at: 1:33:25 PM~

jeff
4/29/2010
2:05:55 AM

Very nice Glenn!

Last time we spoke I think you were running JCapper on a Mac under a Windows Emulator, no? Been thinking about doing that myself.

One sql command that I've been tinkering with is AVG... which can be used to compare a value in your sql expression against the average numeric value found in a single column for every record in the table.

Not knowing (or even caring) what the average value in the table is for F07, the following sql expression returns all records in the table where numeric value for F07 is at least 25 points higher than the average:


query start:         4/28/2010 10:49:09 PM
query end:           4/28/2010 10:49:25 PM
elapsed time:        16 seconds
~
Data Window Settings:
999 Divisor  Odds Cap: None
~
SQL:  SELECT * FROM STARTERHISTORY
      WHERE (VALF07 - 25) >= (SELECT AVG(VALF07) FROM STARTERHISTORY)
~
~
Data Summary         Win     Place      Show
Mutuel Totals    5010.70   4765.80   4531.90
Bet             -4970.00  -4970.00  -4970.00
Gain               40.70   -204.20   -438.10
~
Wins                 600      1024      1326
Plays               2485      2485      2485
PCT                .2414     .4121     .5336
~
ROI               1.0082    0.9589    0.9119
Avg Mut             8.35      4.65      3.42

-jp

.

GlennF
4/29/2010
8:02:17 AM

Yes, I run Jcapper on my Macbook running Windows XP although technically it is not considered emulation. Heres a link explaining running windows on a Mac.

http://www.apple.com/macosx/compatibility/

FWIW its my understanding that handicapping great ( and your handicapping software competitor ;-) ) Michael Pizzola does his handicapping on a Mac running Windows.

Thanks for the tip about the AVG function in SQL. It definitely looks worth experimenting with.

ArlJim78
5/3/2010
10:47:57 PM

I do something similar with the Date function on all my SQL udm's

When I'm creating the UDM I'm normally running it against the entire starterhistory file. No date filters.

When I'm satisfied with what I've created, I put in this expression at the beginning;
AND DATE>=DATEADD('D',-DATEDIFF('D',#1/1/2010#,NOW()),NOW())

In the above example I would have created the UDM on Jan first 2010, and subsequent data window queries only look back to 1-1-2010.
I set the date equal to the day the UDM was created, so every time I run a data window query it is looking at results from my UDM creation date forward. This way I'm always looking at the UDM performance against fresh data, data that wasn't used to create the UDM.

You can adjust that date in the window if you want to view a different time period, say looking back over the most recent month, week, etc.

JCapper Message Board

JCapper 101 -- Tips on using SQL from a SQL newbie

JCapper 101
-- Tips on using SQL from a SQL newbie