Maybe Select top 1 * from appointment where appointment between (date/time and date/time) order by appointment_date
Ben Conner 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:319768 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
