Re: Simple Question for a Oracle/SQL Guru

From: <pberetta_at_my-deja.com>
Date: Thu, 09 Dec 1999 00:34:50 GMT
Message-ID: <82mtfa$2el$1_at_nnrp1.deja.com>

Jas,
  If all else fails, you might try;

Select A1.Patient_no,
       P1.Patient_no,
       A1.Sv_date,
       A1.D_name             -- add doctor name to output
from   Appointment A1,
       Patient P1

where A1.Patient_no = P1.Patient_no
-- because we are going to mix output for both doctors
 and ( A1.D_name ='Miss Brown'

       or
       A1.D_name = 'Mr Hussein' )
and    A1.Sv_date Between to_date('01-AUG-99') and
       to_date('30-SEP-99')

and 0 < ( Select count (*)
-- patient had at least one appointment with Brown

            from   Appointment a2,
            where A2.Patient_no = A1.Patient_no
             and  A2.D_name ='Miss Brown'
             and   A2.Sv_date Between to_date('01-AUG-99') and
                   to_date('30-SEP-99') )

and 0 < ( Select count (*)
-- patient also had at least 1 appointment with Hussein

            from   Appointment a3,
            where A3.Patient_no = A1.Patient_no
             and  A3.D_name ='Mr Hussein'
             and   A3.Sv_date Between to_date('01-AUG-99') and
                   to_date('30-SEP-99') );

May not be the most elegant solution, but it should give you the desired result. Also note, Oracle dates are stored with a time element (Hours, minutes, seconds). So unless you are absolutely certain all Appointment.Sv_date values carry a 000000 time element you should consider using TRUNC(Appointment.Sv_date) BETWEEN TRUNC(to_date(low)) and TRUNC(to_date(high)), otherwise your return set will NOT include appointements on the last day of the period with time elements > 000000. Hope this helps,
Paul

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 09 1999 - 01:34:50 CET