From: Ed Tenholder
Sent: Saturday, February 08, 2014 1:44 PM
To: 'sqlite-users@sqlite.org'
Subject: Need Help with Golf Handicap Calculation
I’m just trying to learn SQL, and after lots of google searches and reading
posts on this email list, I’ve gotten pretty close.
Table: CREATE TABLE Scores (ScoreID Integer Primary Key,ScoreDate
Text,Player Text,CourseName Text,TeeName Text,Score Integer,Rating Real,Slope
Integer);
Query: SELECT MAX(ScoreDate),AVG((Score-Rating)*(113.0/Slope))*.96 FROM
(SELECT * FROM (SELECT * FROM (SELECT ScoreDate,Score,Rating,Slope FROM Scores
WHERE Player="Joe Smith" ORDER BY ScoreDate ASC LIMIT 3) ORDER BY ScoreDate
DESC LIMIT 20) ORDER BY (Score-Rating)*(113.0/Slope) ASC LIMIT 10)
Result: MAX(ScoreDate) AVG((Score-Rating)*(113.0/Slope))*.96
2000-05-16
29.2436825396825
Logic:
• Select the oldest N scores (3 in the example above)
• From that, select the 20 newest scores
• From that, select the 10 lowest handicap-indexes:
(Score-Rating)*(113/Slope)
• Return the lowest ScoreDate and the average of the handicap-indexes
multiplied by .96
The first SELECT is there because I am going to execute this query iteratively,
substituting for the “3”, from 1 to the count of total records (so I can
create a chart of the change in handicap over time)
The flaw is that the ScoreDate that is returned is the oldest date in the
lowest 10 records, and what I need is the oldest date in the most recent 20
records (from the sub-query).
I cannot figure out how to do this without breaking up the query using temp
tables (which I can do, but I am interested in learning more about SQL and I’m
sure there must be a way to do this (if you can solve Soduko puzzles!)
Thanks for any help,
Ed t.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
