DROP VIEW APRIL_PROJECTIONS;

--Execute the reconstruction for April 1 1996 in a view
CREATE VIEW April_PROJECTIONS
 ( PROJECTION_ID, PROJECTION_NAME, PROJECTION_TYPE, SPHEROID_CODE,
   PROJECTION_UOM, ZONE_CODE )
AS (
 SELECT PROJECTION_ID, PROJECTION_NAME, PROJECTION_TYPE,
        SPHEROID_CODE, PROJECTION_UOM, ZONE_CODE
 FROM PROJECTIONS P
 WHERE
  NOT EXISTS (SELECT *
              FROM P_Audit A
              WHERE P.PROJECTION_ID = A.PROJECTION_ID AND A.When_Changed
		        > TO_DATE('04/01/1996','MM/DD/YYYY') )
UNION 
 SELECT PROJECTION_ID, PROJECTION_NAME, PROJECTION_TYPE,
        SPHEROID_CODE, PROJECTION_UOM, ZONE_CODE 
 FROM P_Audit A 
 WHERE When_Changed = (SELECT MIN(When_Changed) 
                       FROM P_Audit A2 
                       WHERE A.PROJECTION_ID = A2.PROJECTION_ID AND 
                       A2.When_Changed > TO_DATE('04/01/1996','MM/DD/YYYY') )
);
/
