Thanks!!!!!!! It's working great!! I tested it for case1, case2 and case3 PatientIDs and for all of them returns the nearest!!

Svein Erling Tysvaer <svein.erling.tysvaer@...> escribió: That's a slightly interesting problem, Alejandro, let's see:

select T1.PatientID, T1.DateIni, min(T1.Dateend), min(T1.Days)
from ThisTable T1
where not exists(select * from ThisTable T2
where T2.PatientID = T1.PatientID
and T2.Days between T1.Days+1 and 365)
group by 1, 2

I.e. I let the where clause remove all records that are not the highest
days below 365, and then use MIN to find the first occurence. I think
the result should be the highest days < 365 or lowest days >= 365. Try
it and report back whether it works.

I assumed that if there were a record with 365, this should be returned
(you didn't say what to return in case of equality).

HTH,
Set

Alejandro Garcia wrote:

> Hi! I'm trying to solve this:
> I have this table
> PatientID DateIni Dateend Days
> For each PatientID there are many Dateend but only one DateIni.
> Days is the difference between Dateend and DateIni.
> I need to find for each PatientID the Dateend which Days value is nearest=
> to 365.
> =20
> So for each PatientID I can have 3 cases:
> 1.For all the Dateend all the Days value are less than 365 return the grea=
> test Dateend
> 2. There are Dateend which Days value is less than 365 and other which are =
> greater than 365, return the greatest Dateend that's less than 365
> 3. All the Dateend have all the Days value greater than 365 return the less=
> Dateend
> I could GROUP BY Patientid and if for each PatientID there's at least on=
> e Dateend which Days <365 return MAX(Dateend) else return MIN (Dateend)
> But I can't loop in each group and can't create that groups for looping..

---------------------------------

LLama Gratis a cualquier PC del Mundo.
Llamadas a fijos y móviles desde 1 céntimo por minuto.
http://es.voice.yahoo.com

[Non-text portions of this message have been removed]