Thanks to everyone, I got it working. For the sake of posterity, here
is what I came up with:
SELECT a.*
FROM appointment a INNER JOIN
(SELECT patient_id, MIN(appointment_date) AS appointment_date
FROM appointment
GROUP BY patient_id, convert(varchar(10), appointment_date,
101)) b
ON a.patient_id = b.patient_id
AND a.appointment_date = b.appointment_date
ORDER BY a.patient_id, a.appointment_date
Still have some testing to make sure that everything is included
properly but this seems to hit the spot.
Cheers,
Judah
On Tue, Feb 24, 2009 at 2:51 PM, Judah McAuley <ju...@wiredotter.com> wrote:
> I like this approach. That middle query inserting into the temp table
> might be what I need for the view.
>
> Thanks,
> Judah
>
> On Tue, Feb 24, 2009 at 12:48 PM, <b...@bradwood.com> wrote:
>>
>> 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:319776
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
