Subject | Re: [firebird-support] Nearest to a fixed value |
---|---|

Author | Svein Erling Tysvaer |

Post date | 2007-04-27T11:31:29Z |

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:

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