Nearest to a fixed value

venerdì 27 aprile 2007 - 12.36

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

aleplgr Profilo | Junior Member

51 messaggi | Data Invio: ven 27 apr 2007 - 12:36

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.

So for each PatientID I can have 3 cases:
1.For all the Dateend all the Days value are less than 365 return the greatest 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 one Dateend which Days <365 return MAX(Dateend) else return MIN (Dateend)
But I can't manage to loop in each group and can't create that groups for looping

alx_81 Profilo | Guru

8.814 messaggi | Data Invio: ven 27 apr 2007 - 19:52

>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.
>
>So for each PatientID I can have 3 cases:
>1.For all the Dateend all the Days value are less than 365 return
>the greatest 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 one Dateend which Days <365 return MAX(Dateend) else
>return MIN (Dateend)
>But I can't manage to loop in each group and can't create that
>groups for looping
Hi,
tell us what's your dbms, pls..
bye

Alx81 =)

http://blogs.dotnethell.it/suxstellino

aleplgr Profilo | Junior Member

51 messaggi | Data Invio: lun 30 apr 2007 - 09:01

I'm in InterBase 7.5, But in most of my problems I just need to use SQL statements that are common to SQL Server without (I hope) having to change it a lot..

Don't worry about my last post, it's solved by this:

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

Torna su

Stanze Forum

Elenco Threads

Partecipa anche tu! Registrati!

Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !