ViperSBT
-
Posts
49 -
Joined
-
Last visited
Never
Posts posted by ViperSBT
-
-
Do to the size and quantity of data I sent it via e-mail. I did not send all of the data due to it being more than 5 years of information...
-
Completely understand.
-
Yes, you are on it now... I can comprehend what you are saying needs to happen, but I have no idea how to make that happen. I thank you very much for your assistance and look forward to any guidance you, or anyone else for that matter, can give me.
-
dog entries Descending date DATEDIFF( MAX( date ) , MIN( t.date ) ) 1489 45 2006-11-12 1267 709 25 2006-09-16 1247 1155 24 2005-09-24 946 2941 22 2007-03-16 847 1918 21 2006-05-27 1492 1346 19 2007-12-01 938
This is what it kicked out... The values in entries are still showing based on all of the dog's entries...
-
Not sure exactly how to do that... I am still pretty green behind the ears...
-
It doesn't like the HAVING put in the SELECT.
-
I guess that is part of the problem. As for the math, I am not sure what you are meaning. The logic is the tricky part. As I understand it the DATEDIFF is currently making sure that a dog has entered at least two events with a difference in dates greater than four days, and if that case is true, then the query is counting all of the dog's entries...
I guess in writing this it has cleared my head of what the logic needs to be...
I need it to count all entries that have a date within 4 days of each other as one entry. Does that make sense?
DOG
DATE
1
8/20/2010
1
8/21/2010
1
8/13/2010
1
8/14/2010
1
8/7/2010
1
8/8/2010
1
8/1/2010
1
8/2/2010
2
8/20/2010
2
8/13/2010
2
8/14/2010
2
8/7/1010
2
8/1/2010
3
8/20/2010
3
8/13/2010
3
8/14/2010
3
8/8/2010With the above data the results I am currently getting are:
DOG 1 = 8, DOG 2 = 5, DOG 3 = 4
The desired results would be:
DOG 1 = 4, DOG 2 = 4, DOG 3 = 3
I hope that helps clarify what I am trying to do better.
-
SELECT s.dog, COUNT(s.dog) AS entries, t.date FROM singles s INNER JOIN tournament t ON t.enumber = s.event GROUP BY s.dog HAVING DATEDIFF( MAX( date ) , MIN( t.date ) ) >4 ORDER BY entries DESC
OK this query give me a count of all the times a dog has entered an event. What I am trying to do is identify the number of times a dog has entered events that were more than 4 days apart from each other.
What am I missing?
-
Anyone? Am I trying to do something that is simply impossible?
-
I have been trying to come up with some way of doing this... I am thinking that 'HAVING' might be applicable here, but can't seem to make it work...
-
I have the following query:
SELECT r.club, MIN(r.besttime) as time, c.cname, t.date FROM racing r JOIN clubs c ON r.club = c.cnumber INNER JOIN tournament t ON t.enumber = r.event && t.date >= '$limit' && r.besttime < 99.999 GROUP BY r.club HAVING DATEDIFF( MAX( date ) , MIN( t.date ) ) >4 ORDER BY time, c.cname
Which generates the following output:
club time cname date83 0.000 BH 2005-07-08
146 0.000 MRRFB 2006-03-25
179 0.000 PS 2006-09-23
22 0.000 S 2005-04-30
2 14.963 TNG 2005-04-30
239 15.127 IR 2007-05-26
75 15.359 HF 2005-11-12
7 15.403 RD 2005-05-14
6 15.479 RDS 2005-05-14
The racing table has an entry for every event that a club has participated in. I need the results to only show me clubs that have participated in more than one event and at least two of those events are more than 7 days apart from each other.
It seems like there should be a way to do this, but I am lucky I have gotten as far as I have...
-
Yes, you are on track. The 2 events criterion is satisfied by ensuring that the (diff)erence between event dates is >4.
Once we have the list of clubs for 18 months, we need to analyze the best times of that list but only looking at the last 12 months for the times.
-
Fenway, any luck with this? I kinda understand where you are going with this, but not sure how to go about putting it into action. I really do appreciate the help.
-
OK, I'll buy that JOINs don't work well with ranges, but how else would you do this query?
-
I don't know why I am not "JOIN the derived tables", I am still very green at all this and only know enough to be dangerous.
As for the explanation of the query:
SELECT r.club, MIN(r.besttime) as time, c.cname, t.date
Here I am looking for the club id, the fastest time that the club has ever run, the club name, and the date of the event that they ran that time.
INNER JOIN tournament t ON t.enumber = r.event && t.date >= '2007-08-01' && r.besttime < 99.999
This join is to limit the scope of the club's fastest time to a period of the last 12 months. So, even though I may have times for them stretching back 3 years, I only want their fastest time during the last 12 months. In production I am using a variable here instead of a fixed date.
WHERE r.club IN (SELECT r.club FROM racing r INNER JOIN tournament t ON t.enumber = r.event && t.date >= '2007-01-01' && r.besttime < 99.999 GROUP BY r.club HAVING DATEDIFF( MAX( date ) , MIN( t.date ) ) >4)
This part of the query is doing a check against all the entries I have in the racing table to only provide me with those clubs that have entered more than one event during the last 18 months (again in production I am using a variable) and those events had to be more than 4 days apart from each other.
So in plain language I am looking for all clubs that have competed in more than two events, greater than 4 days apart, during the last 18 months and need to know the fastest time they ran during the last 12 months.
-
I tried this, which seems to work and is correct, but it takes about 10 minutes to process:
SELECT r.club, MIN(r.besttime) as time, c.cname, t.date FROM racing r INNER JOIN clubs c ON r.club = c.cnumber INNER JOIN tournament t ON t.enumber = r.event && t.date >= '2007-08-01' && r.besttime < 99.999 WHERE r.club IN (SELECT r.club FROM racing r INNER JOIN tournament t ON t.enumber = r.event && t.date >= '2007-01-01' && r.besttime < 99.999 GROUP BY r.club HAVING DATEDIFF( MAX( date ) , MIN( t.date ) ) >4) GROUP BY r.club ORDER BY time, c.cname
-
Is there not a way that I could pull my query that gets the MIN(besttime) from the last 12 months using the query that figures out the clubs that have participated in more than two events seperated by at least 4 days?
-
So it looks like I can only limit this to one date set...
-
When I have tried doing that it limits the first date selection.
-
Thought I would try to clarify a little further where I need to go with this query. I need to take the results of this query and then identify the besttime of each of the clubs since the date of 2007-08-01.
-
SELECT r.club, MIN(r.besttime) as time, c.cname, t.date FROM racing r JOIN clubs c ON r.club = c.cnumber INNER JOIN tournament t ON t.enumber = r.event && t.date >= '2007-01-01' && r.besttime < 99.999 GROUP BY r.club HAVING DATEDIFF( MAX( date ) , MIN( t.date ) ) >4 ORDER BY time, c.cname
The above query generates a list of clubs that have participated in more than two events that are greater than 4 days apart over the last 18 months. However, I need to limit the MIN(r.besttime) to only look at the last 12 months. Any thoughts?
-
OK, I need to figure out how many people attended my events on different weekends....
For the people I have a table:
Table: attendance Fields: id person event
For the events table:
Table: events Fields: id name date
I can handle doing a query like:
SELECT a.person, e.name, e.date FROM attendance a JOIN events e ON a.event = e.id
So, that gives me a history of all the people and the event and date they have attended.
What I am looking for is a history of all the people that have attended multiple events on dates that are greater than 4 days apart from each other. Can this be done in a query?
-
Thanks... It took me a bit to understand what you were trying to tell me, but I got it figured out.
THANK YOU!!!
-
OK, here is the query I am trying to execute:
SELECT b.breed AS Breed, d.rname AS RegisteredName, s.time AS Time, CONCAT(h.fname, ' ', h.lname) AS Owner, h.street1 AS Street, h.city AS City , s1.pc AS State, h .zip AS ZipCode, c.cname AS Club FROM singles s INNER JOIN(SELECT b1.breed, MIN(s1.time) AS besttime FROM singles s1 JOIN dogs d1 ON s1.dog = d1.dnumber JOIN breeds b1 ON d1.breed = b1.id GROUP BY b1.breed) AS fastest ON s.time = fastest.besttime JOIN dogs d ON s.dog = d.dnumber JOIN breeds b ON d.breed = b.id JOIN handler h ON d.owner = h.hnumber JOIN tournament t ON s.event = t.enumber JOIN state s1 ON h.state = s1.id JOIN clubs c ON c.cnumber = d.club WHERE t.date LIKE '2007%' ORDER BY b.breed, s.time ASC
What I am trying to do is to identify the fastest dog (by time) for each breed of dog in that has competed. This current query gives me each of the dog's fastest time for each of the breeds that has competed. I only want the fastest one for each breed. What am I missing?
Need help with this query and DATEDIFF function
in MySQL Help
Posted
The second (untested) query didn't returned an empty result...
The first however returned what I believe to be a list of all dogs that have competed in at least two tournaments that had dates greater then 4 days apart. Which is a step in the right direction, but I need to be able to count the number of times that each individual dog entered a tournament that wasn't within 4 days of another tournament. Does that make sense? Sorry it has taken me so long to get back to you, just been crazy....