This is the sort of approach I was trying to think of, couldn't remember derived tables. This query only grabs the earliest appointment for each patient though not the earliest for every day. But it is a good starting point, I'll try to massage a group by date in there and see what I can come up with.
Thanks, Judah On Tue, Feb 24, 2009 at 12:52 PM, Ben Conner <b...@webworldinc.com> wrote: > > Hi Judah, > > I suppose there's more than one way to do this, but this should work... > > SELECT a.* > FROM appointment a INNER JOIN > (SELECT patient_id, MIN(appointment_date) > AS appointment_date > FROM appointment > GROUP BY patient_id) b ON a.patient_id = > b.patient_id AND a.appointment_date = b.appointment_date > > --Ben > > Judah McAuley wrote: >> 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 >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:319772 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
