Help with Dates

[tsyle]

Here is my sample data

Patient_ID Visit_Number Last_Visit
----------- ------------ -----------
9283 1 Jan 20 2003
6372 2 Jan 20 2003
7347 3 Jan 20 2003
2372 4 Jan 20 2003
8493 5 Jan 20 2003
4283 6 Jan 20 2003
7698 7 Jan 20 2003
5382 8 Jan 20 2003
8392 9 Jan 20 2003
3238 10 Jan 21 2003
4283 11 Jan 21 2003
2322 12 Jan 21 2003
9283 13 Jan 21 2003
7698 14 Jan 21 2003
6293 15 Jan 21 2003
7347 16 Jan 21 2003
1328 17 Jan 21 2003
5382 18 Jan 22 2003
9283 19 Jan 22 2003
8493 20 Jan 22 2003
1382 21 Jan 22 2003
7347 22 Jan 22 2003
8392 23 Jan 22 2003
3832 24 Jan 22 2003
9283 25 Jan 22 2003

What i need to do is find the Patient_ID who had the longest time in days between appointments. I been trying to figure this out for my work, but haven't been able to get it yet. I got a feeling that it's something simple too.

 

[mrdenny]

This should do the trick.

select Patient_ID, Min(Last_Visit), max(Last_Visit), datediff(dd, min(Last_Visit), max(Last_Visit))
from table
group by Patient_ID
order by 4 DESC

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[vongrunt]

What i need to do is find the Patient_ID who had the longest time in days between appointments.

Between first and last appointment (mrdenny's code), or between any two subsequent appointments for that patient?

And if two or more patients have the same longest interval, do you want 'em all?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[tsyle]

Yes if 2 or more patients have the same longest interval then i want them all. As for the first question, between two subsequent appointments.

 

[vongrunt]

Then problem is more complex than you probably think - inefficient self-join and ranking is required. I'm afraid this code will run slowly on larger data sets but try it anyway:

declare @maxdiff int

select top 1 @maxdiff = datediff(dd, A.Last_Visit, min(B.Last_Visit))
from Visits A
inner join Visits B on A.Patient_ID=B.Patient_ID and A.Last_Visit < B.Last_Visit
group by A.Patient_ID, A.Last_Visit
order by datediff(dd, A.Last_visit, min(B.Last_Visit)) desc

select A.Patient_ID, @maxdiff as diff, A.Last_Visit
from Visits A
inner join Visits B on A.Patient_ID=B.Patient_ID and A.Last_Visit < B.Last_Visit
group by A.Patient_ID, A.Last_Visit
having datediff(dd, A.Last_Visit, min(B.Last_Visit)) = @maxdiff

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[tsyle]

Wow that code is complicating. I didn't think it be that complicating, but thank you. I will try that.