WITH
address_max_date as (
      SELECT
            CONTROL_ID,
            SSN,
            ADDRESS_KEY,
            MAX(EFF_DATE) EFF_DATE
      FROM ADDRESS_EFF_DATE
      WHERE EFF_DATE<=SYSDATE
        AND ADDRESS_KEY=0
      GROUP BY CONTROL_ID, SSN, ADDRESS_KEY
),
addresses as (
      SELECT
          CONTROL_ID,
          SSN,
          ADDRESS_1,
          ADDRESS_2,
          ADDRESS_3,
          CITY,
          STATE,
          ZIP,
          TELEPHONE,
          COUNTY,
          COUNTRY,
          EFF_DATE as ADDRESS_EFF_DATE
      FROM ADDRESS_EFF_DATE
      NATURAL JOIN address_max_date
)

WITH
addresses as (
      SELECT
          CONTROL_ID,
          SSN,
          ADDRESS_1,
          ADDRESS_2,
          ADDRESS_3,
          CITY,
          STATE,
          ZIP,
          TELEPHONE,
          COUNTY,
          COUNTRY,
          EFF_DATE as ADDRESS_EFF_DATE
      FROM (
        SELECT ADDRESS_EFF_DATE.*,
               MAX(EFF_DATE) OVER(PARTITION BY CONTROL_ID, SSN) AS MAX_EFF_DATE
        FROM ADDRESS_EFF_DATE
        WHERE EFF_DATE <= SYSDATE
          AND ADDRESS_KEY = 0
      )
      WHERE EFF_DATE = MAX_EFF_DATE
)

WITH
addresses as (
      SELECT
          CONTROL_ID,
          SSN,
          ADDRESS_1,
          ADDRESS_2,
          ADDRESS_3,
          CITY,
          STATE,
          ZIP,
          TELEPHONE,
          COUNTY,
          COUNTRY,
          EFF_DATE as ADDRESS_EFF_DATE
      FROM (
        SELECT ADDRESS_EFF_DATE.*,
               ROW_NUMBER() OVER(PARTITION BY CONTROL_ID, SSN ORDER BY EFF_DATE DESC) AS ROW_NUMBER
        FROM ADDRESS_EFF_DATE
        WHERE EFF_DATE <= SYSDATE
          AND ADDRESS_KEY = 0
      )
      WHERE ROW_NUMBER = 1
)

WITH
addresses as (
    SELECT
          CONTROL_ID,
          SSN,
          ADDRESS_1,
          ADDRESS_2,
          ADDRESS_3,
          CITY,
          STATE,
          ZIP,
          TELEPHONE,
          COUNTY,
          COUNTRY,
          EFF_DATE as ADDRESS_EFF_DATE
    FROM address_eff_date a WHERE eff_date = (
        SELECT MAX(eff_date)
        FROM ADDRESS_EFF_DATE
        WHERE CONTROL_ID = a.CONTROL_ID
            AND SSN = a.SSN
            AND ADDRESS_KEY = 0
            AND EFF_DATE <= SYSDATE )
)

create table ADDRESS_EFF_DATE
(
  control_id     VARCHAR2(10) not null,
  ssn            VARCHAR2(10) not null,
  address_key    NUMBER(2) not null,
  address_1      VARCHAR2(75),
  address_2      VARCHAR2(75),
  city           VARCHAR2(50),
  state          VARCHAR2(2),
  zip            VARCHAR2(10),
  telephone      VARCHAR2(20),
  county         VARCHAR2(10),
  country        VARCHAR2(45),
  address_3      VARCHAR2(75),
  from_date      DATE,
  to_date        DATE,
  eff_date       DATE default TO_DATE('01/01/1900','MM/DD/YYYY') not null,
  transaction_id NUMBER
);
create index ADDRESS_EFF_DATE_IDX on ADDRESS_EFF_DATE (SSN);
create index ADDRESS_EFF_DATE_IDX1 on ADDRESS_EFF_DATE (SSN, ADDRESS_KEY, EFF_DATE DESC);
create index ADDRESS_EFF_DATE_TID on ADDRESS_EFF_DATE (TRANSACTION_ID);
alter table ADDRESS_EFF_DATE add constraint ADDRESS_EFF_DATE_PK primary key (CONTROL_ID, SSN, ADDRESS_KEY, EFF_DATE);
alter table ADDRESS_EFF_DATE add constraint ADDRESS_EFF_DATE_ODC foreign key (CONTROL_ID, SSN) references EMP_SSN (CONTROL_ID, SSN) on delete cascade;