Detecting duplicates which fall outside of a date interval

Question

I searched in SO but couldnt find a direct answer.

There are patients, hospitals, medical branches(ER,urology,orthopedics,internal disease etc), medical operation codes (examination,surgical operation, MRI, ultrasound or sth. else) and patient visiting dates.

Patient visits doctor, doctor prescribes medicine and asks to come again for control check. If patient returns after 10 days, (s)he has to pay another examination fee to the same hospital. Hospitals may appoint a date after 10 days telling there are no available slots in following 10 days, in order to get the examination fee.

Table structure is like:

Patient id.no   Hospital    Medical Branch     Medical Op. Code      Date
  1                 H1             M0                P1            01/05/2011
  5                 H1             M1                P9            03/05/2011
  3                 H2             M0                P2            09/05/2011
  1                 H1             M0                P1            14/05/2011 
  3                 H1             M0                P2            20/05/2011
  5                 H1             M2                P9            25/05/2011
  1                 H1             M0                P3            26/05/2011

Here, visiting patients no. 3 and 5 does not constitute a problem as patient no. 3 visits different hospitals and patient no.5 visits different medical branches. They would pay the examination fee even if they visited within 10 days.

Patient no.1, however, visits same hospital, same branch and is subject to same process (P1: examination) on 01/05 and 14/05.

26/05 doesnt count because it is not medical examination.

What I want to flag is same patient, same hospital, same branch and same medical operation code (that is specifically medical examination : P1 ), with date range more than 10 days.

The format of resulting table:

HOSPITAL        TOTAL NUM. of PATIENTS      NUM. of PATIENTS OUT OF DATE RANGE
  H1                        x                              a
  H2                        y                              b
  H3                        z                              c

Thanks.

Answer 1

Once again, it's analytic functions to the rescue.

This query uses the LAG() function to link a record in YOUR_TABLE with the previous (defined by DATE) matching record (defined by PATIENT_ID) in the table.

select hospital_id
       , count(*) as total_num_of_patients      
       , sum (out_of_range) as num_of_patients_out_of_range
from (
    select patient_id
           , hospital_id
           , case
                   when hospital_id_1 = hospital_id_0
                   and  visit_1 > visit_0 + 10
                   and  med_op_code_1 = med_op_code_0
                   then 1
                   else 0
              end as out_of_range
    from (
            select patient_id
                   , hospital_id as hospital_id_1
                   , date as visit_1
                   , med_op_code as med_op_code_1
                   , lag (date) over (partition by patient_id order by date) as visit_0
                   , lag (hopital_id) over (partition by patient_id order by date) as hopital_id_0
                   , lag (med_op_code) over (partition by patient_id order by date) as med_op_code_0
            from your_table
            where med_op_code = 'P1'
        )
    )
group by hospital_id
/

Caveat: I haven't tested this code, so it may contain syntax errors. I will check it the next time I can access an Oracle database.