EOBasser's on Fire!

I am curious, As the tournament progresses will the AOTY program just have the highest point total, of all fish entered, as the leader or will it determine the "actual" leader based on the highest 10 fish average?  

Good question. I hope it shows the actual leader based on that person's top 10 scoring fish.

Yep, your overall score is based only on your top ten species.  

Here's the code to prove it.

Code: [Select]

SELECT id as user_id, users.login, Q1.Score from users left join (SELECT user_id, login, sum(maxspscore)
 as score FROM (SELECT t1.user_id, t1.login, t1.speciesname, t1.maxspscore, COUNT(*) AS lowerscore FROM
(SELECT users.id as user_id, users.login, targets.speciesname, Max(length*targets.points) AS maxspscore FROM
users INNER JOIN (targets INNER JOIN catches ON targets.id = catches.target_id) ON users.id = catches.user_id
WHERE (catches.date >= users.date_paid) AND (catches.date >= '2010-01-01') and (users.date_paid >= '2010-
01-01') GROUP BY users.login, targets.speciesname) AS t1 JOIN (SELECT users.id as user_id, users.login,
targets.speciesname, Max(length*targets.points) AS maxspscore FROM users INNER JOIN (targets INNER JOIN
catches ON targets.id = catches.target_id) ON users.id = catches.user_id WHERE  (catches.date >=
users.date_paid) AND (catches.date >= '2010-01-01') and (users.date_paid >= '2010-01-01') GROUP BY
users.login, targets.speciesname) AS t2 ON t1.user_id = t2.user_id AND t1.maxspscore <= t2.maxspscore
GROUP BY t1.user_id, t1.maxspscore HAVING lowerscore <=10) AS Table1 GROUP BY user_id order by score
DESC) as Q1 ON users.id = Q1.user_id where date_paid >='2010-01-01' order by Q1.score DESC, users.login;


Yep, your overall score is based only on your top ten species.  

Here's the code to prove it.

Code: [Select]

SELECT id as user_id, users.login, Q1.Score from users left join (SELECT user_id, login, sum(maxspscore)
 as score FROM (SELECT t1.user_id, t1.login, t1.speciesname, t1.maxspscore, COUNT(*) AS lowerscore FROM
(SELECT users.id as user_id, users.login, targets.speciesname, Max(length*targets.points) AS maxspscore FROM
users INNER JOIN (targets INNER JOIN catches ON targets.id = catches.target_id) ON users.id = catches.user_id
WHERE (catches.date >= users.date_paid) AND (catches.date >= '2010-01-01') and (users.date_paid >= '2010-
01-01') GROUP BY users.login, targets.speciesname) AS t1 JOIN (SELECT users.id as user_id, users.login,
targets.speciesname, Max(length*targets.points) AS maxspscore FROM users INNER JOIN (targets INNER JOIN
catches ON targets.id = catches.target_id) ON users.id = catches.user_id WHERE  (catches.date >=
users.date_paid) AND (catches.date >= '2010-01-01') and (users.date_paid >= '2010-01-01') GROUP BY
users.login, targets.speciesname) AS t2 ON t1.user_id = t2.user_id AND t1.maxspscore <= t2.maxspscore
GROUP BY t1.user_id, t1.maxspscore HAVING lowerscore <=10) AS Table1 GROUP BY user_id order by score
DESC) as Q1 ON users.id = Q1.user_id where date_paid >='2010-01-01' order by Q1.score DESC, users.login;


Looks good to me, I'm convinced! 

Here's the code to prove it.

Code: [Select]

SELECT id as user_id, users.login, Q1.Score from users left join (SELECT user_id, login, sum(maxspscore)
 as score FROM (SELECT t1.user_id, t1.login, t1.speciesname, t1.maxspscore, COUNT(*) AS lowerscore FROM
(SELECT users.id as user_id, users.login, targets.speciesname, Max(length*targets.points) AS maxspscore FROM
users INNER JOIN (targets INNER JOIN catches ON targets.id = catches.target_id) ON users.id = catches.user_id
WHERE (catches.date >= users.date_paid) AND (catches.date >= '2010-01-01') and (users.date_paid >= '2010-
01-01') GROUP BY users.login, targets.speciesname) AS t1 JOIN (SELECT users.id as user_id, users.login,
targets.speciesname, Max(length*targets.points) AS maxspscore FROM users INNER JOIN (targets INNER JOIN
catches ON targets.id = catches.target_id) ON users.id = catches.user_id WHERE  (catches.date >=
users.date_paid) AND (catches.date >= '2010-01-01') and (users.date_paid >= '2010-01-01') GROUP BY
users.login, targets.speciesname) AS t2 ON t1.user_id = t2.user_id AND t1.maxspscore <= t2.maxspscore
GROUP BY t1.user_id, t1.maxspscore HAVING lowerscore <=10) AS Table1 GROUP BY user_id order by score
DESC) as Q1 ON users.id = Q1.user_id where date_paid >='2010-01-01' order by Q1.score DESC, users.login;


So, ummm, where's the code for latest entries and looking at people's uploaded images?   

Yeah,  I need to spend a couple hours tweeking things for NWKA.  The upgrade didn't go as smoothly as I hoped.  As soon as I find time. 

I am curious, As the tournament progresses will the AOTY program just have the highest point total, of all fish entered, as the leader or will it determine the "actual" leader based on the highest 10 fish average?  

Good question. I hope it shows the actual leader based on that person's top 10 scoring fish.

Yep, your overall score is based only on your top ten species.  

Here's the code to prove it.

Code: [Select]

SELECT id as user_id, users.login, Q1.Score from users left join (SELECT user_id, login, sum(maxspscore)
 as score FROM (SELECT t1.user_id, t1.login, t1.speciesname, t1.maxspscore, COUNT(*) AS lowerscore FROM
(SELECT users.id as user_id, users.login, targets.speciesname, Max(length*targets.points) AS maxspscore FROM
users INNER JOIN (targets INNER JOIN catches ON targets.id = catches.target_id) ON users.id = catches.user_id
WHERE (catches.date >= users.date_paid) AND (catches.date >= '2010-01-01') and (users.date_paid >= '2010-
01-01') GROUP BY users.login, targets.speciesname) AS t1 JOIN (SELECT users.id as user_id, users.login,
targets.speciesname, Max(length*targets.points) AS maxspscore FROM users INNER JOIN (targets INNER JOIN
catches ON targets.id = catches.target_id) ON users.id = catches.user_id WHERE  (catches.date >=
users.date_paid) AND (catches.date >= '2010-01-01') and (users.date_paid >= '2010-01-01') GROUP BY
users.login, targets.speciesname) AS t2 ON t1.user_id = t2.user_id AND t1.maxspscore <= t2.maxspscore
GROUP BY t1.user_id, t1.maxspscore HAVING lowerscore <=10) AS Table1 GROUP BY user_id order by score
DESC) as Q1 ON users.id = Q1.user_id where date_paid >='2010-01-01' order by Q1.score DESC, users.login;


Curious how I add a small upgrade and my total decreased? I'm not too sure about that code... 

Nope, that's right Jay.  Keep adding fish, and watch your points move over to mine.   

Curious how I add a small upgrade and my total decreased? I'm not too sure about that code... 

It's a feature Jay.  You'll learn to love it.  

Actually, there's a faulty screen in the code.  When someone enters two fish that equal the same number of points on a given day, the software automatically drops one of the entries.  It bit me earlier this year when my SMB equaled my Chinook (thus the funky precision on my 1st SMB).
Brian mentioned it earlier in the year.  At the time neither one of us thought it was a likely scenario.  I guess this goes to prove that us Oregon yakfishers are a fish catching anamoly.

-Spot-

Curious how I add a small upgrade and my total decreased? I'm not too sure about that code... 

It's a feature Jay.  You'll learn to love it.  

Actually, there's a faulty screen in the code.  When someone enters two fish that equal the same number of points on a given day, the software automatically drops one of the entries.  It bit me earlier this year when my SMB equaled my Chinook (thus the funky precision on my 1st SMB).
Brian mentioned it earlier in the year.  At the time neither one of us thought it was a likely scenario.  I guess this goes to prove that us Oregon yakfishers are a fish catching anamoly.

-Spot-

I'm just a seasick anomaly. Yesterday made it 3/3 this year. I haven't actually hurled but got so weak and green that it was an effort to get back in. It takes all the fun out of it. Not to mention limits effective fishing time to 1-2 hours.    I'm going to give in and medicate again. At least that keeps me out there.

The upgrade ling was only 1.25" bigger so it's not a lot of points but going backwards? That how my fishing felt yesterday so I guess it fits...

It turns out that the point value for the upgraded ling was the same as a previous entry from a previous day so it subtracted it rather than change the larger fish for the smaller fish. I used Spot's laser measurement system, updated the length in AOTY and that solved the problem...