Jump to content

ViperSBT

Members
  • Posts

    49
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

ViperSBT's Achievements

Member

Member (2/5)

0

Reputation

  1. 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....
  2. 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...
  3. 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.
  4. 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...
  5. Not sure exactly how to do that... I am still pretty green behind the ears...
  6. It doesn't like the HAVING put in the SELECT.
  7. 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/2010 With 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.
  8. 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?
  9. Anyone? Am I trying to do something that is simply impossible?
  10. 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...
  11. 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: 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...
  12. 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.
  13. 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.
  14. OK, I'll buy that JOINs don't work well with ranges, but how else would you do this query?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.