From the email inbox:
--quote:"Morning Jeff. I am trying to write a SQL query. I am not having luck with and "racedays<=20" I am getting results going back to the first of the year.
I am looking for the last 20 race days at a track to find what is working." --end quote
Use the DateDiff keyword.
The example below uses 10 days.
But you can modify the number of days as needed
Notes:
The DateDiff keyword has 3 arguments inside the parenthesis characters:
1. The time interval. Here I used D inside of single quotes to indicate number of days:
WHERE DATEDIFF('D', [DATE], #03-24-2024#) <= 10
2. The date in the data being queried. Here I used the DATE column in the table enclosed in square brackets:
WHERE DATEDIFF('D', [DATE], #03-24-2024#) <= 10
3. The date you are comparing the query data to. Here I used today's date enclosed in pound signs:
WHERE DATEDIFF('D', [DATE], #03-24-2024#) <= 10
Alternately, you can also use NOW like this:
WHERE DATEDIFF('D', [DATE], NOW) <= 10
Lastly, you need to add the number of days. Here I used <= 10 to indicate 10 days like this:
WHERE DATEDIFF('D', [DATE], #03-24-2024#) <= 10
And of course you can adjust the number of days as needed:
<= 20
, etc.
Cut and paste from the Data Window:
query start: 3/24/2024 8:02:44 AM
query end: 3/24/2024 8:02:48 AM
elapsed time: 4 seconds
Data Window Settings:
Connected to: C:\JCapper\exe\JCapper2.mdb
999 Divisor Odds Cap: None
SQL UDM Plays Report: Hide
SQL: SELECT * FROM STARTERHISTORY
WHERE DATEDIFF('D', [DATE], #03-24-2024#) <= 10
Data Summary Win Place Show
-----------------------------------------------------
Mutuel Totals 9326.40 9734.08 9777.72
Bet -12914.00 -12914.00 -12914.00
-----------------------------------------------------
P/L -3587.60 -3179.92 -3136.28
Wins 858 1712 2512
Plays 6457 6457 6457
PCT .1329 .2651 .3890
ROI 0.7222 0.7538 0.7571
Avg Mut 10.87 5.69 3.89
By: Calendar Timeline Analysis
>=Min < Max P/L Bet Roi Wins Plays Pct Impact
------------------------------------------------------------------------------------
-999 1 0.00 0.00 0.0000 0 0 .0000 0.0000
1 2 -367.14 1926.00 0.8094 118 963 .1225 0.9221 <-day 1 03-22-2024
2 3 -586.24 1748.00 0.6646 119 874 .1362 1.0247
3 4 -192.28 1168.00 0.8354 75 584 .1284 0.9665
4 5 -290.16 1092.00 0.7343 76 546 .1392 1.0475
5 6 -193.30 484.00 0.6006 35 242 .1446 1.0884
6 7 -156.60 488.00 0.6791 35 244 .1434 1.0795
7 8 -412.60 1238.00 0.6667 87 619 .1405 1.0577
8 9 -626.64 1786.00 0.6491 113 893 .1265 0.9523
9 10 -566.40 1762.00 0.6785 118 881 .1339 1.0080
10 11 -196.24 1222.00 0.8394 82 611 .1342 1.01001 <-day 10 03-13-2024
-jp
.
| 