ViperSBT Posted August 19, 2010 Share Posted August 19, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/ Share on other sites More sharing options...
fenway Posted August 20, 2010 Share Posted August 20, 2010 Have you confirmed your math and logic is sound? Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1101695 Share on other sites More sharing options...
ViperSBT Posted August 21, 2010 Author Share Posted August 21, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1102030 Share on other sites More sharing options...
peter162in Posted August 21, 2010 Share Posted August 21, 2010 SELECT DISTINCT s.dog, COUNT(s.dog) AS entries, t.date I think some where u have to use DISTINCT... not sure.. Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1102124 Share on other sites More sharing options...
fenway Posted August 22, 2010 Share Posted August 22, 2010 Drop the GROUP BY, move the having expression to your select list, and see if you get what you expect. Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1102215 Share on other sites More sharing options...
ViperSBT Posted August 22, 2010 Author Share Posted August 22, 2010 It doesn't like the HAVING put in the SELECT. Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1102417 Share on other sites More sharing options...
fenway Posted August 22, 2010 Share Posted August 22, 2010 It doesn't like the HAVING put in the SELECT. I meant the expression you're evaluating, not the clause itself. Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1102438 Share on other sites More sharing options...
ViperSBT Posted August 23, 2010 Author Share Posted August 23, 2010 Not sure exactly how to do that... I am still pretty green behind the ears... Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1102745 Share on other sites More sharing options...
fenway Posted August 26, 2010 Share Posted August 26, 2010 See what this returns: SELECT s.dog, COUNT(s.dog) AS entries, t.date, DATEDIFF( MAX( date ) , MIN( t.date ) ) FROM singles s INNER JOIN tournament t ON t.enumber = s.event GROUP BY s.dog Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1103903 Share on other sites More sharing options...
ViperSBT Posted August 27, 2010 Author Share Posted August 27, 2010 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... Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1104220 Share on other sites More sharing options...
fenway Posted August 30, 2010 Share Posted August 30, 2010 Oh, now I understand what you want. At first glance, you may have to use user-variables to handle this -- which means sorting the result set by ( dog, date ) -- and simply checking the difference for a given dog for each row. I'm just heading out, so I don't have time to draft the query, but basically, you reset the prev date if the dog is different, otherwise, check the prev vs current date, and mark accordingly. Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1105222 Share on other sites More sharing options...
ViperSBT Posted August 30, 2010 Author Share Posted August 30, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1105330 Share on other sites More sharing options...
fenway Posted August 31, 2010 Share Posted August 31, 2010 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. Likely won't have an opportunity for a few days.... Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1105756 Share on other sites More sharing options...
ViperSBT Posted September 6, 2010 Author Share Posted September 6, 2010 Completely understand. Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1107994 Share on other sites More sharing options...
fenway Posted September 15, 2010 Share Posted September 15, 2010 Sorry, I know it's been a while -- can you post the create table statements and some insert statements so that I can re-create this scenario on my end? Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1111364 Share on other sites More sharing options...
ViperSBT Posted September 16, 2010 Author Share Posted September 16, 2010 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... Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1111871 Share on other sites More sharing options...
fenway Posted September 17, 2010 Share Posted September 17, 2010 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... A sample of few hundred records would have sufficed.... Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1111952 Share on other sites More sharing options...
fenway Posted September 20, 2010 Share Posted September 20, 2010 OK, sorry it took so long. Now that I've played with it a bit, I don't think you need user variables -- I was thinking of rankings, and that's not actually what you want. Basically, if you join all the dog/dates to themselves, any time you get a condition where the dates differ by 4 days for a given dog, you want that dog back. If that's the case, this should work: select distinct t1.dog from ( SELECT s.dog,t.date FROM singles s INNER JOIN tournament t ON t.enumber = s.event ) t1 inner join ( SELECT s.dog,t.date FROM singles s INNER JOIN tournament t ON t.enumber = s.event ) t2 on ( t1.dog = t2.dog ) where t1.date > t2.date and DATEDIFF( t1.date, t2.date ) > 4 order by t1.dog, t1.date The date comparison in the where clause is simply to ensure the temporal order of the two dates, so that the DATEDIFF() will never be negative; and it neatly cuts down the number of rows to examine. Does that make sense? Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1113257 Share on other sites More sharing options...
fenway Posted September 20, 2010 Share Posted September 20, 2010 Actually, you probably don't need derived tables -- try this (untested): SELECT DISTINCT s.dog FROM singles s INNER JOIN tournament t1 ON t1.enumber = s.event INNER JOIN tournament t2 ON t2.enumber = s.event where t1.date > t2.date and DATEDIFF( t1.date, t2.date ) > 4 Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1113313 Share on other sites More sharing options...
ViperSBT Posted September 30, 2010 Author Share Posted September 30, 2010 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.... Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1117548 Share on other sites More sharing options...
fenway Posted September 30, 2010 Share Posted September 30, 2010 The second (untested) query didn't returned an empty result... Not surprised, I typed it from somewhere other than my dev machine -- I'll take another look. 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? I don't think you've ever mentioned that before... Quote Link to comment https://forums.phpfreaks.com/topic/211231-need-help-with-this-query-and-datediff-function/#findComment-1117737 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.