Home » SQL & PL/SQL » SQL & PL/SQL » outer join help
outer join help [message #209437] Thu, 14 December 2006 17:15 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I have the following query that is pulling all vital signs after a patient gets a 'heart cath'. The problem I'm having is that there might be 20 blood
pressures but only 15 pulses taken, and I'm only getting results where the perform_date
for the BP = PULSE. So assuming all perform_ddt's match for the above BP & PULSEs, I'm only getting 15 blood pressure results and the other five aren't being reported. How can I fix this to show all results even if there's a BP for a specific time but no pulse or visa versa? I want to report all vital signs and have them match on date/time when there is a match and show a blank if there is not match on date/time.
columns.

Thanks,
Stan





SELECT
   DEMO.Patient_Name,
   DEMO.Dept_Room_Bed,
   DEMO.patient_id,
   DEMO.dept_id,
   BP.BP,
   BP.perform_date,
   PULSE.PULSE,
   PULSE.perform_date
FROM

--  Demographics

   (SELECT DISTINCT
      patient.pat_seq,
      patient.dept_id,
      patient.dept_id ||'-'|| patient.room_id ||'-'|| patient.bed_id AS Dept_Room_Bed,
      INITCAP(patient.last_name ||', '|| patient.first_name ||' '|| patient.middle_name) AS Patient_Name,
      patient.patient_id,
      MSD.MSD
   FROM
      patient,
      (SELECT  -- PULLING MINIMUM CHART DATE FOR SHEATH PATIENTS
         pr.pat_seq,
         pr.perform_ddt,
         (MIN(pr.perform_ddt) OVER (PARTITION BY p.pat_seq)) MSD
      FROM
         pcq_label pcql,
         patient p,
         pat_results pr,
         staff_local sl
      WHERE
         pr.pat_seq = p.pat_seq AND
         pr.label_seq = pcql.label_seq AND
         pr.chart_id = sl.staff_id AND
         pr.label_seq IN (150106) AND
         pr.pat_seq IN 
         (SELECT /*+ PUSH_SUBQ */ 
            pat_seq 
         FROM 
            patient 
         WHERE 
                 facility_id = 'F'  
            AND (discharge_dt > TRUNC(SYSDATE-1) OR discharge_dt IS NULL))) MSD
   WHERE  
          patient.pat_seq = MSD.pat_seq
      AND patient.dept_id NOT IN ('TRNG','NYP','NRS')
   ORDER BY
      Dept_Room_Bed) DEMO,

-- Blood Pressure query

   (SELECT 
      pr.pat_seq,
      (MAX(pr.result_value) ||'  -  '|| INITCAP(sl.staff_name)) BP,
      DDT.TOCHAR(pr.perform_ddt,'MM/DD/YYYY HH24:MI') Perform_Date
   FROM
      pcq_label pcql,
      patient p,
      pat_results pr,
      staff_local sl,
      (SELECT  -- PULLING MINIMUM CHART DATE FOR SHEATH PATIENTS
         pr.pat_seq,
         pr.perform_ddt,
         (MIN(pr.perform_ddt) OVER (PARTITION BY p.pat_seq)) MSD
      FROM
         pcq_label pcql,
         patient p,
         pat_results pr,
         staff_local sl
      WHERE
         pr.pat_seq = p.pat_seq AND
         pr.label_seq = pcql.label_seq AND
         pr.chart_id = sl.staff_id AND
         ((pr.perform_ddt = DDT.FROMDATE(TRUNC(P.ADMIT_DT))) OR (pr.perform_ddt > DDT.FROMDATE(TRUNC(P.ADMIT_DT))))  AND
         pr.label_seq IN (150106) AND
         pr.pat_seq IN 
         (SELECT /*+ PUSH_SUBQ */ 
            pat_seq 
         FROM 
            patient 
         WHERE 
                 facility_id = 'F'  
            AND (discharge_dt > TRUNC(SYSDATE-1) OR discharge_dt IS NULL)))MSD
   WHERE
      pr.pat_seq=p.pat_seq  AND
      MSD.pat_seq = pr.pat_seq AND
      ((pr.perform_ddt = MSD.MSD) OR (pr.perform_ddt > MSD.MSD)) AND
      pr.label_seq=pcql.label_seq  AND
      pr.chart_id=sl.staff_id  AND
      pr.label_seq IN ('6881','9761','9638')
   GROUP BY
      sl.staff_name,
      pr.perform_ddt,
      pr.label_seq,
      pr.pat_seq
   ORDER BY
      pr.pat_seq,
      perform_date)BP,
      
-- PULSE query

   (SELECT
      pr.pat_seq,
      (MAX(pr.result_value) ||'  -  '|| INITCAP(sl.staff_name)) PULSE,
      DDT.TOCHAR(pr.perform_ddt,'MM/DD/YYYY HH24:MI') Perform_Date
   FROM
      pcq_label pcql,
      patient p,
      pat_results pr,
      staff_local sl,
      (SELECT  -- PULLING MINIMUM CHART DATE FOR SHEATH PATIENTS
         pr.pat_seq,
         pr.perform_ddt,
         (MIN(pr.perform_ddt) OVER (PARTITION BY p.pat_seq)) MSD
      FROM
         pcq_label pcql,
         patient p,
         pat_results pr,
         staff_local sl
      WHERE
         pr.pat_seq = p.pat_seq AND
         pr.label_seq = pcql.label_seq AND
         pr.chart_id = sl.staff_id AND
         ((pr.perform_ddt = DDT.FROMDATE(TRUNC(P.ADMIT_DT))) OR (pr.perform_ddt > DDT.FROMDATE(TRUNC(P.ADMIT_DT))))  AND
         pr.label_seq IN (150106) AND
         pr.pat_seq IN 
         (SELECT /*+ PUSH_SUBQ */ 
            pat_seq 
         FROM 
            patient 
         WHERE 
                 facility_id = 'F'  
            AND (discharge_dt > TRUNC(SYSDATE-1) OR discharge_dt IS NULL)))MSD
   WHERE
      pr.pat_seq=p.pat_seq  AND
      MSD.pat_seq = pr.pat_seq AND
      pr.label_seq=pcql.label_seq  AND
      ((pr.perform_ddt = MSD.MSD) OR (pr.perform_ddt > MSD.MSD)) AND
      pr.chart_id=sl.staff_id  AND
      pr.label_seq IN ('6879')
   GROUP BY
      sl.staff_name,
      pr.perform_ddt,
      pr.pat_seq
   ORDER BY
      pr.pat_seq,
      perform_date) PULSE

WHERE
   DEMO.pat_seq = BP.pat_seq AND 
   DEMO.pat_seq = PULSE.pat_seq AND
   BP.perform_date = PULSE.perform_date(+)


[Updated on: Fri, 15 December 2006 14:24]

Report message to a moderator

Re: outer join help [message #209754 is a reply to message #209437] Sun, 17 December 2006 10:15 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I figured it out. It was with my join....this is how I got the desired results:

((demo.pat_seq = bp.pat_seq(+)) and (demo.perform_ddt = bp.perform_ddt(+))) and
((demo.pat_seq = pulse.pat_seq(+)) and (demo.perform_ddt = pulse.perform_ddt(+)))

Thanks for looking,
Stan
Previous Topic: case in oracle
Next Topic: Exclude records from the beginning and the end of a table
Goto Forum:
  


Current Time: Sat Dec 10 03:07:44 CST 2016

Total time taken to generate the page: 0.12006 seconds