CODEWITHSUNDEEP
sqloracle-databasegaps-and-islands
Saud
Saud

Reputation: 89

SQL Oracle: how to sort patient transfer data

I want you to help me writing this query about patient transfers in the units. As most of you know, the patient can be transferred from one unit to another unit and also can be transferred from bed to another bed in the same unit, but for this query I want only the unit transfers and to have the first arrival and the last update for each transfer in each different unit and to ignore any bed to bed transfers, here is an example:

PATIENT_ID  Start_Date   END_DATE   UNIT_NAME   BED_NUMBER
----------------------------------------------------------
1           01/01/2015   01/02/2015     A          1
1           01/02/2015   01/03/2015     A          2    
1           01/03/2015   01/03/2015     B          1
1           01/03/2015   01/04/2015     C          5
1           01/04/2015   01/06/2015     C          8
1           01/06/2015   01/07/2015     C          9
1           01/07/2015   01/08/2015     A          1

The output should be:

PATIENT_ID  Start_Date   END_DATE     UNIT_NAME
------------------------------------------------
1           01/01/2015   01/03/2015      A
1           01/03/2015   01/03/2015      B
1           01/03/2015   01/07/2015      C
1           01/07/2015   01/08/2015      A

Please note that the patient transfers can be done multiple times within the same day.

Upvotes: 0

Views: 138

Answers (1)

Noel
Noel

Reputation: 10525

This is problem. You can use Tabibitosan method for this. Refer the link for complete explanation of this method.

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table hospi(
    patien_id   number,
    start_date  date,
    end_date    date,
    unit_name   varchar2(1),
    bed_number  number
);

insert into hospi values(1, date'2015-01-01', date'2015-01-02',  'A',  1);
insert into hospi values(1, date'2015-01-02', date'2015-01-03',  'A',  2);
insert into hospi values(1, date'2015-01-03', date'2015-01-03',  'B',  1);
insert into hospi values(1, date'2015-01-03', date'2015-01-04',  'C',  5);
insert into hospi values(1, date'2015-01-04', date'2015-01-06',  'C',  8);
insert into hospi values(1, date'2015-01-06', date'2015-01-07',  'C',  9);
insert into hospi values(1, date'2015-01-07', date'2015-01-08',  'A',  1);
insert into hospi values(2, date'2015-01-07', date'2015-01-08',  'A',  1);
insert into hospi values(2, date'2015-01-08', date'2015-01-08',  'B',  1);
insert into hospi values(2, date'2015-01-08', date'2015-01-09',  'B',  1);

Query:

select
    patien_id,
    min(start_date)     start_date,
    max(end_date)       end_date,
    unit_name
from (
    select 
        patien_id,
        start_date,
        end_date,
        unit_name,
        row_number() over (partition by patien_id order by start_date, end_date) -
        row_number() over (partition by patien_id, unit_name order by start_date, end_date)     grp
    from hospi
)
group by
    patien_id,
    unit_name,
    grp
order by
    patien_id,
    start_date,
    end_date

Results:

| PATIEN_ID |                START_DATE |                  END_DATE | UNIT_NAME |
|-----------|---------------------------|---------------------------|-----------|
|         1 | January, 01 2015 00:00:00 | January, 03 2015 00:00:00 |         A |
|         1 | January, 03 2015 00:00:00 | January, 03 2015 00:00:00 |         B |
|         1 | January, 03 2015 00:00:00 | January, 07 2015 00:00:00 |         C |
|         1 | January, 07 2015 00:00:00 | January, 08 2015 00:00:00 |         A |
|         2 | January, 07 2015 00:00:00 | January, 08 2015 00:00:00 |         A |
|         2 | January, 08 2015 00:00:00 | January, 09 2015 00:00:00 |         B |

Note: If you dont store the time part in start_date and end_date, and if there are many transfers between units on same day, You cannot determine the order in which the transfer occurs.

Upvotes: 3

Related Questions