Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sorting mySQL Query & sub Queries by date range

Posted on 2009-07-06
21
Medium Priority
?
422 Views
Last Modified: 2013-12-13
Hi Experts,

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
 
FROM Player,
 (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";

Open in new window

0
Comment
Question by:PHP_Idiot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
  • 2
  • +1
21 Comments
 
LVL 7

Expert Comment

by:pdoelle
ID: 24790054
Have you tried putting the dates in single quotes?
SELECT * FROM `tablename` WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'

Open in new window

0
 
LVL 17

Expert Comment

by:nanharbison
ID: 24790571
you have to convert the dates to a form that allows you to perform comparison, I use this little function that I found so you can enter a date in the form 2009-07-06 and it converts it to a time stamp that can be compared to other dates in the same form.
So use this function on both end date of your date range, and then convert the returned date from the database, and then compare them.

function mysqldate_to_unix_timestamp($date) {
 
    list($year, $month, $day) = explode('-', $date);
    return mktime(0, 0, 0, $month, $day, $year);
}

Open in new window

0
 

Author Comment

by:PHP_Idiot
ID: 24792251
Thanks for your input guys,

Pdoelle, well spotted but that was just a typo on my part in the question, I have used ' ' in the queries I'm working with.

nanharbison, again thanks for your input but I use date ranges in other queries use on the same data set and have never needed to convert it to timestamp before, so I don't think it's that either!

To be honest I think I''m just putting it in the wrong place, so if you could offer any advice on the placement of it within the query that would be really helpfull.

Thanks a lot
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 17

Expert Comment

by:nanharbison
ID: 24792722
Where were you putting it when it didn't work?
0
 

Author Comment

by:PHP_Idiot
ID: 24792765
These are two of the options I have tried

Option One:
$query = "SELECT Player.MembershipNo, Results.Date, 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
 
FROM Player,
 (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
 WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
ORDER BY SubSelect.sum_points DESC";

Open in new window

0
 

Author Comment

by:PHP_Idiot
ID: 24792776
Option Two:
$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
 
FROM Player,
 (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, Results.Date, SUM(Position3.Points) AS sum_points3
    FROM Player Player3, Results Results3, Position Position3
   WHERE Player3.MembershipNo = Results3.MembershipNo 
     AND Results3.Position = Position3.Position
	  WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
   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
 WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
ORDER BY SubSelect.sum_points DESC";

Open in new window

0
 

Author Comment

by:PHP_Idiot
ID: 24792790
The reason I tried these was, firstly in option one I figured that putting it at the end would filter everything it had just calculated by the date fields. But when that didn't work I thought that by adding it in the second sub-query it would calculate the points total from those dates and therefore reduce a bit of work it had to do, but that also failed.

I have actually tried the same set up in pretty much every sub-query in there all with not results!
any ideas you have would be greatfully recieved!

Thanks
0
 
LVL 17

Expert Comment

by:nanharbison
ID: 24792836
In option two, you have the date part twice, I assume you meant to remove line 29?
When you say this doesn't work, what results do you get from each of these options? Do you get the dates you are looking for, or no results at all, or what?
0
 

Author Comment

by:PHP_Idiot
ID: 24792889
Your spot on I did forget to remove line 29, but only in the post not in the original query attemp.

I have run both in myphpAdmin and recieved the following errors.
Whe I try it in the main webpage code I just get a blank page, which isn't much help!

Is this the kind of info you needed?
OPTION ONE:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
ORDER BY SubSelect.sum_' at line 28 
 
 
OPTION TWO:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
   GROUP BY Player3.Mem' at line 15 

Open in new window

0
 
LVL 17

Expert Comment

by:nanharbison
ID: 24793448
And you have:
WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'

 but you meant to use:
WHERE Results1.Date BETWEEN '2009-06-05' AND '2009-09-31'
 or
WHERE Results1.Date BETWEEN '2009-06-05' AND '2009-09-31'
?
I am trying to wrap my brain around this query - for the time being, have you taken chunks of this, starting from the most inside SELECT statements and running them in PHPMyAdmin and then building on it? I would start with the one below.

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

Open in new window

0
 

Author Comment

by:PHP_Idiot
ID: 24793513
I'm sure this won't come as any surprise to you to find out I didn't actually write this query!
I had an incredible amount of help and don't truly understand it myself, I just know that it works and shows me what I need! (I know it's lame but I'm trying to learn)

That sub you posted above, show the totals points each player has in each venue. the outpu is membership number column, venue column, and total points scored at venue.

I'll run each sub and let you know what the output is
0
 

Author Comment

by:PHP_Idiot
ID: 24793996
OK I spotted something here..
Instead of using: WHERE Date BETWEEN '2009-01-01' AND '2009-12-12'
I Used : AND Date BETWEEN '2009-01-01' AND '2009-12-12'

The examples are 1: Date sorted on sub alone
2: my attempt to put it in main query!  

This almost works, I get the correct total if I put in dates covering the full season, but if I close the gap to look at a specific date, or week, it reports incorrectly, missing some points out!

I would expect, for example, that in the date range 2009-07-01 and 2009-07-010 I would get two results, both with a total of 750 points from two different venues, but I get only 1 results not two!

I cant work out why it's no longer calculating them correctly!!
ONE: 
SELECT Player2.MembershipNo, Venue2.VenueName, SUM(Position2.Points) AS sum_points2, Results2.Date
                                      FROM Player Player2, Results Results2, Position Position2, Venue Venue2
                                     WHERE Player2.MembershipNo = Results2.MembershipNo
                                       AND Results2.Position = Position2.Position
                                       AND Venue2.VenueID = Results2.VenueID
AND Results2.Date BETWEEN '2009-01-01' AND '2009-12-12'
                                     GROUP BY Player2.MembershipNo, Venue2.VenueID
 
 
TWO:
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
FROM Player, (
 
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, Results2.Date
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
AND Results2.Date
BETWEEN '2009-04-01'
AND '2009-07-30'
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

Open in new window

0
 
LVL 2

Accepted Solution

by:
shoppedude earned 500 total points
ID: 24796884
The problem is that the Results table is accessed in three different parts of the query and must have the date range assigned in all three places. The attached code should resolve the issue.
DECLARE @Begin_Date DateTime;
DECLARE @End_Date DateTime;
 
SET @Begin_Date = '2009-06-28';
SET @End_Date   = '2009-07-03';
 
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
 
FROM Player,
 (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
     AND Results1.Date between @Begin_Date and @End_Date
   GROUP BY Player1.MembershipNo, Venue1.VenueName) 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
     AND Results3.Date between @Begin_Date and @End_Date
   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
                                       AND Results2.Date between @Begin_Date and @End_Date
                                     GROUP BY Player2.MembershipNo, Venue2.VenueName) SubSelect1
                             WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo)
  AND Player.MembershipNo = SubSelect3.MembershipNo
  AND SubSelect.sum_points >= 750
 
ORDER BY SubSelect.sum_points DESC

Open in new window

0
 
LVL 17

Expert Comment

by:nanharbison
ID: 24797656
I had to wait to look this up until now, and I asked around. You can use the date field as is for sorting, but not for comparing dates. Try changing them to strtotime($row['datefield'] first. You don't need the little function I gave you.
http://php.net/manual/en/function.strtotime.php
0
 

Author Comment

by:PHP_Idiot
ID: 24799196
Hi Shoppedude

I ran your version through phpMyAdmin and got an error on the first line, however if I removed the first two lines (the two Declare lines, it worked perfectly. Do I need those two lines? If so why?
I also like the fact the date ranges can be changed in just one place to affect the whole query :-)
0
 

Author Comment

by:PHP_Idiot
ID: 24799207
By the way this was the error message i got:

Error
SQL query:

DECLARE@Begin_Date DateTime;



MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @Begin_Date DateTime' at line 1
0
 
LVL 2

Expert Comment

by:shoppedude
ID: 24799356
The declares are required for MS SQL Server, which is where I do my testing. They're there so that SQL Server knows what type of variables are in use. If the statement works without them in PHP then you're good to go.

-Lee
0
 

Author Closing Comment

by:PHP_Idiot
ID: 31600397
Exactely the result I was looking for, thanks so much for your time and expertise :-)
0
 

Author Comment

by:PHP_Idiot
ID: 24802198
Hi shoppedude
Just want to say a massive thankyou for your time and effort, this is exactely the result I was after.

Also want to say a huge thanks to nanharbison for teaching me several new things around comparing dates, it's wasn't quite what I needed this time, but I'm sure it will be usefull sometime soon.

Thanks a lot
0
 
LVL 17

Expert Comment

by:nanharbison
ID: 24802213
I am glad shoppedude stepped in to help! But FYI, I just heard from the SQL guru of all gurus, and he says definitively you CANNOT accurately compare dates with mathematical operations. You will not get the results you are looking for!
0
 

Author Comment

by:PHP_Idiot
ID: 24803003
I'm not actually comparing dates though, I'm comparing numerical data stored between two dates.
for example, I'm adding all points scored by player 'X' in Venue 'Y' from '2009-06-01' to '2009-07-01'
and checking to see if that total is eual or more than 750, if it is it is displayed, if not it is left out of the table.

The dates only mark the start and end point of the data I need to compare.
I hope that makes sense. What I can say for certain though is that shoppedude's response gives exactely the output I get when I calculate it manually, so it is doing what I needed it to do, and accurately!
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question