Untitled

-- 0% progress
SELECT COUNT(SNA_ID) AS '0% Click Web Apply / Secure Link'
FROM stg_new_application
WHERE sna_curstep LIKE '%PORTALMS_CC_WRKW_NW_OCBC_APPY_FRM%'
  AND MONTH(SNA_CREATEDT) = MONTH(CURRENT_DATE)
  AND YEAR(SNA_CREATEDT) = YEAR(CURRENT_DATE);

-- 20% to 100% progress
SELECT
    CASE
        WHEN sna.sna_curstep IN ('PORTALMS_CC_WRKW_NN_MY_OCBC', 'PORTALMS_CC_WRKW_DC_CHKLST', 'PORTALMS_CC_WRKW_OCBC_FRM_1')
            THEN '20%'
        WHEN sna.sna_curstep IN ('PORTALMS_CC_WRKW_OCBC_FRM_2', 'PORTALMS_CC_WRKW_OCBC_FRM_3')
            THEN '40%'
        WHEN sna.sna_curstep IN ('PORTALMS_CC_WRKW_OCBC_FRM_5', 'PORTALMS_CC_WRKW_OCBC_FRM_6')
            THEN '60%'
        WHEN sna.sna_curstep IN ('PORTALMS_CC_WRKW_OCBC_FRM_7', 'PORTALMS_CC_WRKW_OCBC_FRM_7_GV')
            THEN '80%'
        WHEN sna.sna_curstep IN ('PORTALMS_CC_WRKW_OCBC_APP_DTLS', 'PORTALMS_CC_WRKW_NN_EKYC_OCBC_APP_DTLS')
            THEN '90%'
        WHEN sna.sna_curstep IN ('PORTALMS_CC_WRKW_APP_JRNY_OCBC_EKYC_DSKP', 'PORTALMS_CC_WRKW_APP_JRNY_END_OCBC_EKYC', 'PORTALMS_CC_WRKW_APP_JRNY_END_OCBC')
            THEN '100%'
    END AS progress,
    sna.SNA_ID,
    sna.SNA_CU_NAME,
    sna.SNA_CU_EMAIL,
    sna.SNA_CU_PHONE,
    w.SNS_MONTHLY_INC,
    w.SNS_IC_NUMBER,
    w.SNS_AL_RACE,
    w.SNS_BUMIPUTERA_STS,
    w.SNS_AL_GENDER,
    w.SNS_AL_EDU_LVL,
    w.SNS_CUSTOMER_MART_STATUS,
    w.SNS_AL_MOTHER_NAME,
    w.SNS_AL_EMRGNCY_NAME,
    w.SNS_AL_EMRGNCY_NMBR,
    w.SNS_AL_EMRGNCY_RELAY,
    w.SNS_CARD_TYPE,
    w.SNS_CARD_COLLECT,
    w.SNS_CARD_BRANCH,
    w.SNS_CRED_HIST,
    w.SNS_CRED_HIST_WBANK,
    sna.SNA_PROD_ID,
    w.SNS_REF_CODE,
    sna.SNA_CREATEDT

FROM stg_new_application sna
LEFT JOIN stgnew_sys_cc_wrkw w ON sna.SNA_ID = w.SNS_ID

WHERE sna.sna_curstep IN (
        'PORTALMS_CC_WRKW_NN_MY_OCBC', 'PORTALMS_CC_WRKW_DC_CHKLST', 'PORTALMS_CC_WRKW_OCBC_FRM_1',
        'PORTALMS_CC_WRKW_OCBC_FRM_2', 'PORTALMS_CC_WRKW_OCBC_FRM_3',
        'PORTALMS_CC_WRKW_OCBC_FRM_5', 'PORTALMS_CC_WRKW_OCBC_FRM_6',
        'PORTALMS_CC_WRKW_OCBC_FRM_7', 'PORTALMS_CC_WRKW_OCBC_FRM_7_GV',
        'PORTALMS_CC_WRKW_OCBC_APP_DTLS', 'PORTALMS_CC_WRKW_NN_EKYC_OCBC_APP_DTLS',
        'PORTALMS_CC_WRKW_APP_JRNY_OCBC_EKYC_DSKP', 'PORTALMS_CC_WRKW_APP_JRNY_END_OCBC_EKYC',
        'PORTALMS_CC_WRKW_APP_JRNY_END_OCBC'
    )
  AND MONTH(sna.SNA_CREATEDT) = MONTH(CURRENT_DATE)
  AND YEAR(sna.SNA_CREATEDT) = YEAR(CURRENT_DATE)

ORDER BY progress, sna.SNA_ID DESC;