/* Formatted on 2008/01/16 13:09 (Formatter Plus v4.8.8) */
DROP TABLE airport_name;

CREATE TABLE airport_name
(
  airportkey    NUMBER,
  airport_name  VARCHAR2(10 BYTE),
  start_date    DATE,
  end_date      DATE
);

DROP TABLE airport_owner;

CREATE TABLE airport_owner
(
  airportkey     NUMBER,
  airport_owner  VARCHAR2(10 BYTE),
  start_date     DATE,
  end_date       DATE
);

--
--Example 1
--
TRUNCATE TABLE airport_owner;

INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER1', TO_DATE ('1970', 'YYYY'), TO_DATE ('1972', 'YYYY')
            );
INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER2', TO_DATE ('1972', 'YYYY'), TO_DATE ('1974', 'YYYY')
            );

TRUNCATE TABLE airport_name;

INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME1', TO_DATE ('1970', 'YYYY'), TO_DATE ('1971', 'YYYY')
            );
INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME2', TO_DATE ('1971', 'YYYY'), TO_DATE ('1973', 'YYYY')
            );
INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME3', TO_DATE ('1973', 'YYYY'), TO_DATE ('1974', 'YYYY')
            );

SELECT   *
    FROM (SELECT n.airportkey, n.airport_name, o.airport_owner,
                 GREATEST (n.start_date,
                           NVL (o.start_date, n.start_date)
                          ) start_date,
                 LEAST (n.end_date, NVL (o.end_date, n.end_date)) end_date
            FROM airport_name n JOIN airport_owner o
                 ON n.airportkey = o.airportkey(+)
               AND n.start_date <= o.end_date(+)
               AND n.end_date > o.start_date(+)
                 )
   WHERE start_date <> end_date
ORDER BY start_date;

COMMIT ;


--
--Example 2
--
TRUNCATE TABLE airport_owner;

INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER1', TO_DATE ('1970', 'YYYY'), TO_DATE ('1971', 'YYYY')
            );
INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER2', TO_DATE ('1971', 'YYYY'), TO_DATE ('1973', 'YYYY')
            );
INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER3', TO_DATE ('1973', 'YYYY'), TO_DATE ('1974', 'YYYY')
            );

TRUNCATE TABLE airport_name;

INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME1', TO_DATE ('1970', 'YYYY'), TO_DATE ('1972', 'YYYY')
            );
INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME2', TO_DATE ('1972', 'YYYY'), TO_DATE ('1974', 'YYYY')
            );

SELECT   *
    FROM (SELECT n.airportkey, n.airport_name, o.airport_owner,
                 GREATEST (n.start_date,
                           NVL (o.start_date, n.start_date)
                          ) start_date,
                 LEAST (n.end_date, NVL (o.end_date, n.end_date)) end_date
            FROM airport_name n JOIN airport_owner o
                 ON n.airportkey = o.airportkey(+)
               AND n.start_date <= o.end_date(+)
               AND n.end_date > o.start_date(+)
                 )
   WHERE start_date <> end_date
ORDER BY start_date;


COMMIT ;


--
--Example 3
--
TRUNCATE TABLE airport_owner;

INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER1', TO_DATE ('1970', 'YYYY'), TO_DATE ('1971', 'YYYY')
            );
INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER2', TO_DATE ('1971', 'YYYY'), TO_DATE ('1974', 'YYYY')
            );
INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER3', TO_DATE ('1974', 'YYYY'), TO_DATE ('1975', 'YYYY')
            );

TRUNCATE TABLE airport_name;

INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME1', TO_DATE ('1970', 'YYYY'), TO_DATE ('1972', 'YYYY')
            );
INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME2', TO_DATE ('1972', 'YYYY'), TO_DATE ('1973', 'YYYY')
            );
INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME3', TO_DATE ('1973', 'YYYY'), TO_DATE ('1975', 'YYYY')
            );

SELECT   *
    FROM (SELECT n.airportkey, n.airport_name, o.airport_owner,
                 GREATEST (n.start_date,
                           NVL (o.start_date, n.start_date)
                          ) start_date,
                 LEAST (n.end_date, NVL (o.end_date, n.end_date)) end_date
            FROM airport_name n JOIN airport_owner o
                 ON n.airportkey = o.airportkey(+)
               AND n.start_date <= o.end_date(+)
               AND n.end_date > o.start_date(+)
                 )
   WHERE start_date <> end_date
ORDER BY start_date;


COMMIT ;


--
--Example 4
--
TRUNCATE TABLE airport_owner;

INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER1', TO_DATE ('1970', 'YYYY'), TO_DATE ('1972', 'YYYY')
            );
INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER2', TO_DATE ('1972', 'YYYY'), TO_DATE ('1973', 'YYYY')
            );
INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER3', TO_DATE ('1973', 'YYYY'), TO_DATE ('1975', 'YYYY')
            );

TRUNCATE TABLE airport_name;

INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME1', TO_DATE ('1970', 'YYYY'), TO_DATE ('1971', 'YYYY')
            );
INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME2', TO_DATE ('1971', 'YYYY'), TO_DATE ('1974', 'YYYY')
            );
INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME3', TO_DATE ('1974', 'YYYY'), TO_DATE ('1975', 'YYYY')
            );

SELECT   *
    FROM (SELECT n.airportkey, n.airport_name, o.airport_owner,
                 GREATEST (n.start_date,
                           NVL (o.start_date, n.start_date)
                          ) start_date,
                 LEAST (n.end_date, NVL (o.end_date, n.end_date)) end_date
            FROM airport_name n JOIN airport_owner o
                 ON n.airportkey = o.airportkey(+)
               AND n.start_date <= o.end_date(+)
               AND n.end_date > o.start_date(+)
                 )
   WHERE start_date <> end_date
ORDER BY start_date;


COMMIT ;


--
--Example 5
--
TRUNCATE TABLE airport_owner;

INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER1', TO_DATE ('1970', 'YYYY'), TO_DATE ('1971', 'YYYY')
            );
INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER2', TO_DATE ('1971', 'YYYY'), TO_DATE ('1972', 'YYYY')
            );
INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER3', TO_DATE ('1972', 'YYYY'), TO_DATE ('1973', 'YYYY')
            );

TRUNCATE TABLE airport_name;

INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME1', TO_DATE ('1970', 'YYYY'), TO_DATE ('1971', 'YYYY')
            );
INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME2', TO_DATE ('1971', 'YYYY'), TO_DATE ('1972', 'YYYY')
            );
INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME3', TO_DATE ('1972', 'YYYY'), TO_DATE ('1973', 'YYYY')
            );



SELECT   *
    FROM (SELECT n.airportkey, n.airport_name, o.airport_owner,
                 GREATEST (n.start_date,
                           NVL (o.start_date, n.start_date)
                          ) start_date,
                 LEAST (n.end_date, NVL (o.end_date, n.end_date)) end_date
            FROM airport_name n JOIN airport_owner o
                 ON n.airportkey = o.airportkey(+)
               AND n.start_date <= o.end_date(+)
               AND n.end_date > o.start_date(+)
                 )
   WHERE start_date <> end_date
ORDER BY start_date;



COMMIT ;


--
--Example 6
--
TRUNCATE TABLE airport_owner;

INSERT INTO airport_owner
            (airportkey, airport_owner, start_date, end_date
            )
     VALUES (101, 'OWNER3', TO_DATE ('1972', 'YYYY'), TO_DATE ('1973', 'YYYY')
            );

TRUNCATE TABLE airport_name;

INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME1', TO_DATE ('1970', 'YYYY'), TO_DATE ('1971', 'YYYY')
            );
INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME2', TO_DATE ('1971', 'YYYY'), TO_DATE ('1972', 'YYYY')
            );
INSERT INTO airport_name
            (airportkey, airport_name, start_date, end_date
            )
     VALUES (101, 'NAME3', TO_DATE ('1972', 'YYYY'), TO_DATE ('1973', 'YYYY')
            );



SELECT   *
    FROM (SELECT n.airportkey, n.airport_name, o.airport_owner,
                 GREATEST (n.start_date,
                           NVL (o.start_date, n.start_date)
                          ) start_date,
                 LEAST (n.end_date, NVL (o.end_date, n.end_date)) end_date
            FROM airport_name n JOIN airport_owner o
                 ON n.airportkey = o.airportkey(+)
               AND n.start_date <= o.end_date(+)
               AND n.end_date > o.start_date(+)
                 )
   WHERE start_date <> end_date
ORDER BY start_date;

COMMIT ;