These sort of queries are always kind of annoying. My approach is to to
use an intermediate temp table, group by patient and day, and find the
min() appointment date. Then join that temp table back to your
appointment table to pull out the rest of the information for that first
appointment based on the min date you found. Keep in mind, this
approach ASSUMES that one patient will never have two appointments at
the same time on the same day.
This runs on SQL Server 2000. It is a little crude, but should get the
point across:
declare @appointment table
(id int identity primary key,
patient_id int,
doctor varchar(50),
appointment_date datetime )
declare @first_appointment_per_day table
(patient_id int,
appointment_date datetime)
insert into @appointment (patient_id, doctor, appointment_date)
select 1, 'McDreamy', '2009-02-24 8:00'
union all select 1, 'McSteamy', '2009-02-24 8:30'
union all select 1, 'McRib', '2009-02-25 9:00'
union all select 1, 'McSteamy', '2009-02-25 11:00'
union all select 2, 'McDreamy', '2009-02-24 9:00'
union all select 2, 'McSteamy', '2009-02-24 9:30'
union all select 2, 'McRib', '2009-02-25 7:00'
union all select 2, 'McSteamy', '2009-02-25 7:45'
insert into @first_appointment_per_day
(patient_id, appointment_date)
(select patient_id, min(appointment_date)
from @appointment
group by patient_id, convert(varchar(10), appointment_date, 101))
select app.patient_id, app.appointment_date, app.doctor
from @first_appointment_per_day tmp1
inner join @appointment app on tmp1.patient_id = app.patient_id
and tmp1.appointment_date = app.appointment_date
~Brad
-------- Original Message --------
Subject: Semi-OT: SQL question...Select first item for each person for
each day
From: Judah McAuley <ju...@wiredotter.com>
Date: Tue, February 24, 2009 1:53 pm
To: cf-talk <cf-talk@houseoffusion.com>
What I need to do right now is generate a view that lists only the
first appointment
for each patient each day.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319762
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
