[thelist] SQL: Select first item for each person for each day

[Ken Schaefer]

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