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, <[email protected]> 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 <[email protected]> > Date: Tue, February 24, 2009 1:53 pm > To: cf-talk <[email protected]> > > 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:319774 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
