Solved

Sorting mySQL Query & sub Queries by date range

Posted on 2009-07-06
21
410 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
  • 12
  • 6
  • 2
  • +1
21 Comments
 
LVL 6

Expert Comment

by:pdoelle
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 17

Expert Comment

by:nanharbison
Comment Utility
Where were you putting it when it didn't work?
0
 

Author Comment

by:PHP_Idiot
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:PHP_Idiot
Comment Utility
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
Comment Utility
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 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Exactely the result I was looking for, thanks so much for your time and expertise :-)
0
 

Author Comment

by:PHP_Idiot
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now