-- Eva actually purchased the flat January 3, with transaction splitting

--Do normal insert if there are no overlapping rows that do not contain 
--the period of applicability
INSERT INTO Prop_Owner
SELECT 145, 7797, TO_DATE( '01/03/1998', 'MM/DD/YYYY' ), 
       TO_DATE( '01/10/1998', 'MM/DD/YYYY' ), 
       SYSDATE, TO_DATE( '12/31/9999', 'MM/DD/YYYY' )
FROM DUAL
WHERE NOT EXISTS 
( SELECT * FROM Prop_Owner
  WHERE customer_number = 145 AND 
        property_number = 7797 AND 
        TO_DATE( '01/03/1998', 'MM/DD/YYYY' ) < VT_End AND 
        VT_Begin < TO_DATE( '01/10/1998', 'MM/DD/YYYY' ) AND 
        NOT (VT_Begin < TO_DATE( '01/03/1998', 'MM/DD/YYYY' ) AND 
             TO_DATE( '01/10/1998', 'MM/DD/YYYY' ) < VT_End) AND 
        TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' )
);

--If there is an overlap, extend it, unless PA is contained in PV
INSERT INTO Prop_Owner
SELECT customer_number, property_number,
       LEAST( TO_DATE( '01/03/1998', 'MM/DD/YYYY' ), VT_Begin ),
       GREATEST( TO_DATE( '01/10/1998', 'MM/DD/YYYY' ), VT_End ),
       SYSDATE, TO_DATE( '12/31/9999', 'MM/DD/YYYY' )
FROM Prop_Owner
WHERE customer_number = 145 AND 
      property_number = 7797 AND 
      TO_DATE( '01/03/1998', 'MM/DD/YYYY' ) < VT_End AND 
      VT_Begin < TO_DATE( '01/10/1998', 'MM/DD/YYYY' ) AND 
      NOT (VT_Begin < TO_DATE( '01/03/1998', 'MM/DD/YYYY' ) AND 
           TO_DATE( '01/10/1998', 'MM/DD/YYYY' ) < VT_End) AND 
      TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' );

UPDATE Prop_Owner
SET TT_Stop = SYSDATE
WHERE customer_number = 145 AND 
      property_number = 7797 AND 
      TO_DATE( '01/03/1998', 'MM/DD/YYYY' ) < VT_End AND 
      VT_Begin < TO_DATE( '01/10/1998', 'MM/DD/YYYY' ) AND 
      NOT (VT_Begin < TO_DATE( '01/03/1998', 'MM/DD/YYYY' ) AND 
           TO_DATE( '01/10/1998', 'MM/DD/YYYY' ) < VT_End) AND 
      TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' );

