Need help with this query and DATEDIFF function

[ViperSBT]

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? 

[fenway]

Have you confirmed your math and logic is sound?

[ViperSBT]

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.

[peter162in]

SELECT DISTINCT s.dog, COUNT(s.dog) AS entries, t.date

 

I think some where u have to use DISTINCT... not sure..

[fenway]

Drop the GROUP BY, move the having expression to your select list, and see if you get what you expect.

[ViperSBT]

It doesn't like the HAVING put in the SELECT.

[fenway]

It doesn't like the HAVING put in the SELECT.

I meant the expression you're evaluating, not the clause itself.

[ViperSBT]

Not sure exactly how to do that...  I am still pretty green behind the ears...

[fenway]

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

[ViperSBT]

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...

[fenway]

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.

[ViperSBT]

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.

[fenway]

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....

[ViperSBT]

Completely understand.

[fenway]

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?

[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...

[fenway]

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....

[fenway]

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?

[fenway]

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

[ViperSBT]

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....

[fenway]

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...