Home » SQL & PL/SQL » SQL & PL/SQL » Help with Logic
Help with Logic [message #187056] Thu, 10 August 2006 12:53
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
The following query pulls patients who ‘DO NOT’ have a provider assignment based on the 'greatest start_ddt' and provider_type. The problem is if there’s been more than one assignment at the same time. For example: three people assign themselves at 08/10/2006 07:00. Two (sherrie and jerrie) end there assignments later but the third (berrie) still has an assignment. Sherri and jerrie are showing up in the results even though berrie is still assigned to the patient. Since there’s still an assignment I don’t want sherrie and jerrie showing up on the results. How can I get around this problem?

I can’t wrap my brain around this problem and am hitting a brick wall!

Here are examples of times that staff assigned themselves:

staff.........res1.start_ddt...........res1.end_ddt.........provider_type

sherrie.......08/10/2006 07:00......08/10/2006 10:35.......CI
jerrie........08/10/2006 07:00......08/10/2006 12:45.......SA
berrie........08/10/2006 07:00......08/10/2006 19:00.......RN


SELECT DISTINCT
   res1.Patient_Name,
   res1.Patient_ID,
   res1.Room_Bed,
   res1.admit_dt,
   res1.Age,
   res1.sex,
   res1.provider_type,
   ddt.tochar(res1.start_ddt),
   ddt.tochar(res1.end_ddt)
FROM
   (SELECT 
      patient1.last_name ||', '|| patient1.first_name ||' '|| patient1.middle_name Patient_Name,
      patient1.patient_id AS Patient_ID,
      patient1.room_id || ' - ' || patient1.bed_id Room_Bed,
      assigned_staff1.staff_name || ' - ' || provider_types1.provider_type as Staff_Name,
      patient1.room_id,
      patient1.admit_dt,
      decode(trunc((SYSDATE - patient1.BIRTH_DT)/365.25), 0,
         trunc(months_between(SYSDATE,patient1.BIRTH_DT))||' Months', 1,
         trunc(months_between(SYSDATE,patient1.BIRTH_DT))||' Months', NULL,'Not Available', 
         trunc((SYSDATE - patient1.BIRTH_DT)/365.25)||' Years') Age,
      CASE patient1.sex
         WHEN 'M' THEN 'Male'
         WHEN 'F' THEN 'Female'
      ELSE 'Other'
      END Sex,
      provider_types1.provider_type,
      ccdba.staff_assign1.start_ddt,
      ccdba.staff_assign1.end_ddt,
      MAX(ccdba.staff_assign1.start_ddt)
      OVER 
         (PARTITION BY 
             patient1.last_name|| ',' || patient1.first_name|| ' ' || patient1.middle_name,
             patient1.patient_id) max_start_ddt
      FROM
         patient patient1,
         provider_types provider_types1,
         ccdata.staff_local  assigned_staff1,
         ccdba.staff_assign staff_assign1,
         staff_facility staff_facility1
      WHERE
              patient1.pat_seq = staff_assign1.pat_seq
         AND staff_assign1.staff_seq = assigned_staff1.staff_seq
         AND staff_facility1.provider_type = provider_types1.provider_type
         AND assigned_staff1.staff_seq = staff_facility1.staff_seq
         AND patient1.facility_id  IN ('S')
         AND patient1.dept_id IN ('BUS')
         AND trunc(ddt.todate(ddt.fromdate(patient1.discharge_dt))) IS NULL )res1
WHERE
       res1.start_ddt = res1.max_start_ddt
   AND ddt.tochar(res1.end_ddt,'yyyymmdd hh24:mi') <= to_char(sysdate,'yyyymmdd hh24:mi')
   AND ddt.tochar(res1.start_ddt,'yyyymmdd hh24:mi') <= to_char(sysdate,'yyyymmdd hh24:mi')



Thanks
Stan
Previous Topic: Assigning PL/SQL Table type to Procedure parameter
Next Topic: Local index vs global index in partitioned tables
Goto Forum:
  


Current Time: Fri Dec 09 21:02:39 CST 2016

Total time taken to generate the page: 0.05128 seconds