with t1 AS
(
  SELECT H.CONTRACT_ID,H.H_CREATE_DT FROM DDM.H_CONTRACT H WHERE  H_CREATE_DT BETWEEN TIMESTAMP_FORMAT('2013-08-14 00:00:00',
       'YYYY-MM-DD HH24:MI:SS') AND TIMESTAMP_FORMAT('2013-08-14 23:59:59','YYYY-MM-DD HH24:MI:SS')
),
t2 AS
(
  SELECT CONTRACT_ID, H_CREATE_DT
  FROM DDM.H_CONTRACT
  GROUP BY CONTRACT_ID, H_CREATE_DT
  HAVING (H_CREATE_DT=MAX(H_CREATE_DT) AND H_CREATE_DT<TIMESTAMP_FORMAT('2013-08-14 00:00:00',
       'YYYY-MM-DD HH24:MI:SS')) 
)
,
t3 AS
(
SELECT CONTRACT_ID, H_CREATE_DT FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t2.CONTRACT_ID=t1.CONTRACT_ID)
),
t4 AS
 (
   (SELECT H.*,(0) old_flag FROM DDM.H_CONTRACT H,t1 WHERE H.CONTRACT_ID=t1.CONTRACT_ID AND
                                                         H.H_CREATE_DT=t1.H_CREATE_DT)
    UNION
   (SELECT H.*,(1) old_flag FROM DDM.H_CONTRACT H,t3 WHERE H.CONTRACT_ID=t3.CONTRACT_ID AND
                                                          H.H_CREATE_DT=t3.H_CREATE_DT)
 )
,
t5 AS
 (
   SELECT
      (CASE WHEN  SUM(old_flag)=0
                    THEN COUNT(DISTINCT CURRENCY_TP_CD)
                    ELSE COUNT(DISTINCT CURRENCY_TP_CD)-1
                    END) CURRENCY_TP_CD, 
      (CASE WHEN  SUM(old_flag)=0
                    THEN COUNT(DISTINCT CONTR_LANG_TP_CD)
                    ELSE COUNT(DISTINCT CONTR_LANG_TP_CD)-1
                    END) CONTR_LANG_TP_CD,
      (CASE WHEN  SUM(old_flag)=0
                    THEN COUNT(DISTINCT FREQ_MODE_TP_CD)
                    ELSE COUNT(DISTINCT FREQ_MODE_TP_CD)-1
                    END) FREQ_MODE_TP_CD,
      (CASE WHEN  SUM(old_flag)=0
                    THEN COUNT(DISTINCT BILL_TP_CD)
                    ELSE COUNT(DISTINCT BILL_TP_CD)-1
                    END) BILL_TP_CD,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT REPL_BY_CONTRACT)
                    ELSE COUNT(DISTINCT REPL_BY_CONTRACT)-1
                    END) REPL_BY_CONTRACT,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT PREMIUM_AMT)
                    ELSE COUNT(DISTINCT PREMIUM_AMT)-1
                    END) PREMIUM_AMT,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT NEXT_BILL_DT)
                    ELSE COUNT(DISTINCT NEXT_BILL_DT)-1
                    END) NEXT_BILL_DT,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT CURR_CASH_VAL_AMT)
                    ELSE COUNT(DISTINCT CURR_CASH_VAL_AMT)-1
                    END) CURR_CASH_VAL_AMT,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LINE_OF_BUSINESS)
                    ELSE COUNT(DISTINCT LINE_OF_BUSINESS)-1
                    END) LINE_OF_BUSINESS,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT BRAND_NAME)
                    ELSE COUNT(DISTINCT BRAND_NAME)-1
                    END) BRAND_NAME,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SERVICE_ORG_NAME)
                    ELSE COUNT(DISTINCT SERVICE_ORG_NAME)-1
                    END) SERVICE_ORG_NAME,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT BUS_ORGUNIT_ID)
                    ELSE COUNT(DISTINCT BUS_ORGUNIT_ID)-1
                    END) BUS_ORGUNIT_ID,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SERVICE_PROV_ID)
                    ELSE COUNT(DISTINCT SERVICE_PROV_ID)-1
                    END) SERVICE_PROV_ID,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LAST_UPDATE_DT)
                    ELSE COUNT(DISTINCT LAST_UPDATE_DT)-1
                    END) LAST_UPDATE_DT,
        (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LAST_UPDATE_USER)
                    ELSE COUNT(DISTINCT LAST_UPDATE_USER)-1
                    END) LAST_UPDATE_USER,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LAST_UPDATE_TX_ID)
                    ELSE COUNT(DISTINCT LAST_UPDATE_TX_ID)-1
                    END) LAST_UPDATE_TX_ID,
        (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT ISSUE_LOCATION)
                    ELSE COUNT(DISTINCT ISSUE_LOCATION)-1
                    END) ISSUE_LOCATION,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT ADMIN_CONTRACT_ID)
                    ELSE COUNT(DISTINCT ADMIN_CONTRACT_ID)-1
                    END) ADMIN_CONTRACT_ID,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT ADMIN_SYS_TP_CD)
                    ELSE COUNT(DISTINCT ADMIN_SYS_TP_CD)-1
                    END) ADMIN_SYS_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT PREMAMT_CUR_TP)
                    ELSE COUNT(DISTINCT PREMAMT_CUR_TP)-1
                    END) PREMAMT_CUR_TP,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT CASHVAL_CUR_TP)
                    ELSE COUNT(DISTINCT CASHVAL_CUR_TP)-1
                    END) CASHVAL_CUR_TP,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT ACCESS_TOKEN_VALUE)
                    ELSE COUNT(DISTINCT ACCESS_TOKEN_VALUE)-1
                    END) ACCESS_TOKEN_VALUE,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT MANAGED_ACCOUNT_IND)
                    ELSE COUNT(DISTINCT MANAGED_ACCOUNT_IND)-1
                    END) MANAGED_ACCOUNT_IND,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT AGREEMENT_NAME)
                    ELSE COUNT(DISTINCT AGREEMENT_NAME)-1
                    END) AGREEMENT_NAME,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT AGREEMENT_NICKNAME)
                    ELSE COUNT(DISTINCT AGREEMENT_NICKNAME)-1
                    END) AGREEMENT_NICKNAME,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SIGNED_DT)
                    ELSE COUNT(DISTINCT SIGNED_DT)-1
                    END) SIGNED_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT EXECUTED_DT)
                    ELSE COUNT(DISTINCT EXECUTED_DT)-1
                    END) EXECUTED_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT END_DT)
                    ELSE COUNT(DISTINCT END_DT)-1
                    END) END_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT REPLACES_CONTRACT)
                    ELSE COUNT(DISTINCT REPLACES_CONTRACT)-1
                    END) REPLACES_CONTRACT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT ACCOUNT_LAST_TRANSACTION_DT)
                    ELSE COUNT(DISTINCT ACCOUNT_LAST_TRANSACTION_DT)-1
                    END) ACCOUNT_LAST_TRANSACTION_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT TERMINATION_DT)
                    ELSE COUNT(DISTINCT TERMINATION_DT)-1
                    END) TERMINATION_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT TERMINATION_REASON_TP_CD)
                    ELSE COUNT(DISTINCT TERMINATION_REASON_TP_CD)-1
                    END) TERMINATION_REASON_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT AGREEMENT_DESCRIPTION)
                    ELSE COUNT(DISTINCT AGREEMENT_DESCRIPTION)-1
                    END) AGREEMENT_DESCRIPTION,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT AGREEMENT_ST_TP_CD)
                    ELSE COUNT(DISTINCT AGREEMENT_ST_TP_CD)-1
                    END) AGREEMENT_ST_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT AGREEMENT_TP_CD)
                    ELSE COUNT(DISTINCT AGREEMENT_TP_CD)-1
                    END) AGREEMENT_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SERVICE_LEVEL_TP_CD)
                    ELSE COUNT(DISTINCT SERVICE_LEVEL_TP_CD)-1
                    END) SERVICE_LEVEL_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LAST_VERIFIED_DT)
                    ELSE COUNT(DISTINCT LAST_VERIFIED_DT)-1
                    END) LAST_VERIFIED_DT,
        (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LAST_REVIEWED_DT)
                    ELSE COUNT(DISTINCT LAST_REVIEWED_DT)-1
                    END) LAST_REVIEWED_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT PRODUCT_ID)
                    ELSE COUNT(DISTINCT PRODUCT_ID)-1
                    END) PRODUCT_ID,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT CLUSTER_KEY)
                    ELSE COUNT(DISTINCT CLUSTER_KEY)-1
                    END) CLUSTER_KEY,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SB_SUBDIVISION_TP_CD)
                    ELSE COUNT(DISTINCT SB_SUBDIVISION_TP_CD)-1
                    END) SB_SUBDIVISION_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SB_TB)
                    ELSE COUNT(DISTINCT SB_TB)-1
                    END) SB_TB,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SB_OSB)
                    ELSE COUNT(DISTINCT SB_OSB)-1
                    END) SB_OSB,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SB_VSP)
                    ELSE COUNT(DISTINCT SB_VSP)-1
                    END) SB_VSP             
   FROM t4 
   GROUP BY CONTRACT_ID
 )
 SELECT 
    SUM(CURRENCY_TP_CD) CURRENCY_TP_CD,
    SUM(CONTR_LANG_TP_CD) CONTR_LANG_TP_CD,
    SUM(FREQ_MODE_TP_CD) FREQ_MODE_TP_CD,
    SUM(BILL_TP_CD) BILL_TP_CD,
    SUM(REPL_BY_CONTRACT) REPL_BY_CONTRACT,
    SUM(PREMIUM_AMT) PREMIUM_AMT,
    SUM(NEXT_BILL_DT) NEXT_BILL_DT,
    SUM(CURR_CASH_VAL_AMT) CURR_CASH_VAL_AMT,
    SUM(LINE_OF_BUSINESS) LINE_OF_BUSINESS,
    SUM(BRAND_NAME) BRAND_NAME,
    SUM(SERVICE_ORG_NAME) SERVICE_ORG_NAME,
    SUM(BUS_ORGUNIT_ID) BUS_ORGUNIT_ID,
    SUM(SERVICE_PROV_ID) SERVICE_PROV_ID,
    SUM(LAST_UPDATE_DT) LAST_UPDATE_DT,
    SUM(LAST_UPDATE_USER) LAST_UPDATE_USER,
    SUM(LAST_UPDATE_TX_ID) LAST_UPDATE_TX_ID,
    SUM(ISSUE_LOCATION) ISSUE_LOCATION,
    SUM(ADMIN_CONTRACT_ID) ADMIN_CONTRACT_ID,
    SUM(ADMIN_SYS_TP_CD) ADMIN_SYS_TP_CD,
    SUM(PREMAMT_CUR_TP) PREMAMT_CUR_TP,
    SUM(CASHVAL_CUR_TP) CASHVAL_CUR_TP,
    SUM(ACCESS_TOKEN_VALUE) ACCESS_TOKEN_VALUE,
    SUM(MANAGED_ACCOUNT_IND) MANAGED_ACCOUNT_IND,
    SUM(AGREEMENT_NAME) AGREEMENT_NAME,
    SUM(AGREEMENT_NICKNAME) AGREEMENT_NICKNAME,
    SUM(SIGNED_DT) SIGNED_DT,
    SUM(EXECUTED_DT) EXECUTED_DT,
    SUM(END_DT) END_DT,
    SUM(REPLACES_CONTRACT) REPLACES_CONTRACT,
    SUM(ACCOUNT_LAST_TRANSACTION_DT) ACCOUNT_LAST_TRANSACTION_DT,
    SUM(TERMINATION_DT) TERMINATION_DT,
    SUM(TERMINATION_REASON_TP_CD) TERMINATION_REASON_TP_CD,
    SUM(AGREEMENT_DESCRIPTION) AGREEMENT_DESCRIPTION,
    SUM(AGREEMENT_ST_TP_CD) AGREEMENT_ST_TP_CD,
    SUM(AGREEMENT_TP_CD) AGREEMENT_TP_CD,
    SUM(SERVICE_LEVEL_TP_CD) SERVICE_LEVEL_TP_CD,
    SUM(LAST_VERIFIED_DT) LAST_VERIFIED_DT,
    SUM(LAST_REVIEWED_DT) LAST_REVIEWED_DT,
    SUM(PRODUCT_ID) PRODUCT_ID,
    SUM(CLUSTER_KEY) CLUSTER_KEY,
    SUM(SB_SUBDIVISION_TP_CD) SB_SUBDIVISION_TP_CD,
    SUM(SB_TB) SB_TB,
    SUM(SB_OSB) SB_OSB,
    SUM(SB_VSP) SB_VSP
 FROM t5;