SQL Oracle: how to sort patient transfer data -

I want you to help me write this question about the transfer of patients in units. You know most of us, the patient can be transferred from one unit to another unit and it can also be transferred from bed to another bed in the same unit, but the question I Only Want to transfer unit and for the first time and the last update for each transfer in each separate unit and to avoid any bed for bed transfers, here An example is:

  PATIENT_ID START_DATE to END_DATE UNIT_NAME BED_NUMBER ----- ------------------------ -------------------------- --- 01/01/2015 01/02/2015 A 1 01/02/2015 01 / 03/2015 A 2 1 03/01/2015 01/03/2015 B 1 1 01/03/2015 01 / 04/2015 C5 1 04/01/2015 06/01/2015 C8 1 01.06.2015 07.01.2015 C 9 1 01/07/2015 01/08/2015 A 1  

output:

  PATIENT_ID Start_Date END_DATE UNIT_NAME - ---------------- -------------------------- 01/01/2015 01 03/2015 A1 01/03/2015 01 / 03/2015 B1 2015/01/03 2015/01/07 C1 2015/01/07 2015/01/08 A  

Please note that many times the patient can transfer can be done within the same day.

This problem is that you can use the method for this. Refer to the link for a full description of this method

Oracle 11g R2 Schema Setup :.

  forming table hospital (patin_id number, start date, end date, unit_name varchar2 (1), bed_company number); Enter in the Hospice values ​​(1, Date '2015-01-01', Date '2015-01-02', 'A', 1); Enter in the Hospi values ​​(1, Date '2015-01-02', Date '2015-01-03', 'A', 2); Enter in the Hospi values ​​(1, dated '2015-01-03, dated' 2015-01-03 ',' b ', 1); Enter in Hospi values ​​(1, dated '2015-01-03, dated' 2015-01-04 ',' C ', 5); Enter in the Hospi values ​​(1, dated '2015-01-04', dated '2015-01-06', 'C', 8); Enter in the Hospi values ​​(1, Date '2015-01-06, Date' 2015-01-07 ',' C ', 9); Enter in Hospi values ​​(1, Date '2015-01-07', Date '2015-01-08', 'A', 1); Enter in the Hospi values ​​(2, dated '2015-01-07', dated '2015-01-08', 'A', 1); Enter in the Hospi values ​​(2, dated '2015-01-08, dated' 2015-01-08 ',' B ', 1); Enter in the Hospi values ​​(2, dated '2015-01-08, dated' 2015-01-09 ',' b ', 1);  

query :

  select patien_id, minutes (start_date) start_date, Max (end_date) end_date, the unit_name (patien_id select, start_date, end_date, Unit_name, ROW_NUMBER () by start_date division (Patien_id order, end_date) on - (partition patien_id, unit_name ordered by start_date, end_date) hospital GRP) group patien_id, unit_name, over GRP orders ROW_NUMBER ( ) By patien_id, start_date, end_date  

:

  | PATIEN_ID | START_DATE | END_DATE UNIT_NAME | | ----------- | --------------------------- | --------- ------------------ | ----------- | | 1 | January 01 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, 2015 00:00:00 | B  

Note: If you do not run part of time in start_date and end_date, and if there are many transfers between units on the same day, then you can not set the order in which the transfer it happens. P>