Code: Select all
Select
"ID",
"LastName",
"FirstName",
"#",
"Last_three_Average",
108 - "Last_three_Average" AS "Points",
CASEWHEN("Last_three_Average" > 96, 5,
CASEWHEN("Last_three_Average" > 92, 4,
CASEWHEN("Last_three_Average" > 87, 3,
CASEWHEN("Last_three_Average" > 83, 2,1)))) As "Handicap"
FROM
"GolferContactTBL"
RIGHT JOIN --I switched the Left Join to a Right Join to Get players without rounds played off the list.
(
SELECT
"GolferID", --here we're going to count all * the GolferScore per GolferID and put it
COUNT( * ) as "#", --in the Last_three_Average colume after we average them
AVG( "GolferScore") "Last_three_Average"
FROM
(
SELECT --and those are coming from another select of columes that have GolferID, GolfDate, and GolferScore
"GolferID", --That are from the RoundTBL A and a left Join on a copy of RoundTBL B
"GolfDate", --So then we check with ON if the GolfDate on A is less then or equal to GolfDate on B AND(Both have
"GolferScore" --have to be true) if GolferID A is equal to GolferID B.
FROM
"RoundTBL" "A"
LEFT JOIN
"RoundTBL" "B"
ON
"A"."GolfDate" <= "B"."GolfDate"
AND
"A"."GolferID" = "B"."GolferID"
GROUP BY
"GolferID",
"GolfDate",
"GolferScore"
HAVING COUNT( * ) <= 3
)
GROUP BY
"GolferID"
) AS Q3
ON
"GolferContactTBL"."ID" = "Q3"."GolferID"
ORDER BY
"LastName"
Thanks in advance
Steve Kotsiris