I have the below SQL statement running on my site and it works fine, however, I now need to ammend it to only report back on the information held between two dates.
The query looks at players and their points, it totals a players points for each venue they play in and shows only players with 750 points or more. If they have more than 750 points in more than one venue it selects only the highest scoring venue.
This calculates all our players that qualify for the finals. but season 1 is now over and I need it to now ignore the previous data and only calculate between two dates.
I've tried a bunch of things using a standard WHERE Results.Date BETWEEN 2009-06-05 AND 2009-09-31. But without succes!
I would offer more points...but this is all I have left sorry!!
$query = "SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue,
SubSelect.sum_points as Venue_Points, SubSelect.count_results as Venue_Play_Count, SubSelect3.sum_points3 as Total_Points
(SELECT Player1.MembershipNo, Venue1.VenueName, SUM(Position1.Points) AS sum_points, COUNT(Player1.MembershipNo) AS count_results
FROM Player Player1, Results Results1, Position Position1, Venue Venue1
WHERE Player1.MembershipNo = Results1.MembershipNo
AND Results1.Position = Position1.Position
AND Venue1.VenueID = Results1.VenueID
GROUP BY Player1.MembershipNo, Venue1.VenueID) SubSelect,
(SELECT Player3.MembershipNo, SUM(Position3.Points) AS sum_points3
FROM Player Player3, Results Results3, Position Position3
WHERE Player3.MembershipNo = Results3.MembershipNo
AND Results3.Position = Position3.Position
GROUP BY Player3.MembershipNo) SubSelect3
WHERE Player.MembershipNo = SubSelect.MembershipNo
AND SubSelect.sum_points=(SELECT MAX(SubSelect1.sum_points2)
FROM (SELECT Player2.MembershipNo, Venue2.VenueName, SUM(Position2.Points) AS sum_points2
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
GROUP BY Player2.MembershipNo, Venue2.VenueID) SubSelect1
WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo)
AND Player.MembershipNo = SubSelect3.MembershipNo
AND SubSelect.sum_points >= 750
ORDER BY SubSelect.sum_points DESC";