SQL & PL/SQL

Hi All,

I have patients information in the following manner, this tells us when the patient was admitted and discharged and in between where the patient was transferred i,e one department to another. If the patient is transferred from one department to another the outgoing department is flagged as transfer-out and the new department is flagged as transfer-in.Within each department there are multiple beds, even within same department if the patient is moved from one bed to another this will also be captured as transfer-out and transfer-in, following data for your reference

SQL..

select A.pat_id,A.pat_enc_csn_id,P.hsp_account_id,A.department_id,d.department_name,A.bed_id,

TO_CHAR(A.effective_time, 'MM-DD-YYYY HH24:MI:SS') As admit_dte,

case when A.event_type_c = 1 then 'Admission'

     when A.event_type_c = 2 then 'Discharge'

     when A.event_type_c = 3 then 'Transfer-in'

     when A.event_type_c = 4 then 'Transfer-out'

     when A.event_type_c = 5 then 'Patient-update'

     when A.event_type_c = 6 then 'Census'

     when A.event_type_c = 7 then 'Hospital outpatient'

     when A.event_type_c = 8 then 'Leave of absense'

     else 'Leave of absense return' END AS event_type

from clarity_adt@clarprod A

inner join clarity_dep@clarprod d

on A.department_id = d.department_id

inner join hsp_account@clarprod P

on A.pat_enc_csn_id = P.prim_enc_csn_id

where

P.hsp_account_id = 46131492010

group by A.pat_id,A.pat_enc_csn_id,A.department_id,d.department_name,P.hsp_account_id,A.effective_time,A.bed_id,A.event_type_c

order by admit_dte

Pat_id               Pat_enc_csn_id   Hsp_account               Dept_id               Dept_name                                                 Bed_id            Admit_date                                    Event_type

Now my requirement is to report the patient movement (transfer-in,transfer-out) only at department level and ignore the movement between beds in the same department, below is what I want, taken first 2 departments.

Pat_id                 Pat_enc_csn_id    hsp_account                  dept_id             dept_name                                                             admit_date                                  event_type

Can some one please help me achieving this logic.

Hope I made it clear, using oracle 11g.

Thanks.