[thelist] SQL: Select first item for each person for each day
Ken Schaefer
Ken at adOpenStatic.com
Tue Feb 24 18:57:05 CST 2009
SELECT
TOP 1 b.Appointment_Date, a.Patient
FROM
Patients AS a
INNER JOIN
Appointments AS b
ON
a.Patient_ID = b.Patient_ID
WHERE
CAST(b.Appointment_Date AS Int) = CAST(GetDate() AS Int)
ORDER BY
Appointment_Date ASC
GROUP BY
b.Patient_ID
you'll probably need to debug as Outlook isn't the best IDE :-)
Cheers
Ken
-----Original Message-----
From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Judah McAuley
Sent: Wednesday, 25 February 2009 6:21 AM
To: thelist at lists.evolt.org
Subject: [thelist] SQL: Select first item for each person for each day
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.
Any thoughts?
Thanks in advance,
Judah
More information about the thelist
mailing list