[thelist] SQL: Select first item for each person for each day
jft
jft at worrigee.net
Tue Feb 24 19:22:58 CST 2009
Judah,
Try:
select a1.id, a1.patient_id, a1.doctor, a1.appointment_date
from Appointment a1
where a1.appointment_date =
(select min(a2.appointment_date)
from Appointment a2
where a2.patient_id = a1.patient_id
and cast(a2.appointment_date as date) = cast(a1.appointment_date as date))
and cast(a1.appointment_date as date) = '25 February 2009'
HTH
John
> -------Original Message-------
> From: Judah McAuley <judah at wiredotter.com>
> Subject: [thelist] SQL: Select first item for each person for each day
> Sent: 25 Feb '09 05:20
> I'm ill and having difficulty wrapping my head around an issue I know
> I've done before, so I'm hoping that someone can help me out.
> I have a table that lists patient's appointments.
> Appointment
> ____________
> id
> patient_id
> doctor
> appointment_date (datetime)
> A patient can have 0...n appointments on any given day. What I need to
> do right now is generate a view that lists only the first appointment
> for each patient each day. I'm pretty sure I need to be grouping by a
> day of year function on the appointment_date but my brain is kind of
> losing it on the rest. DB server is Sql Server 2000.
> Thanks in advance,
> Judah
More information about the thelist
mailing list