SELECT 
 TO_CHAR(v2.uabscon_cancellation_date,'MON-YYYY')s_cancel_month,
COUNT(*) s_cont_cancelled,
SUM(v2.NumApps) s_appl_covered,
SUM(DECODE(
          SIGN(TO_DATE('01-FEB-2009')-v2.compl_date),
          1,1,
          0
   )*v2.NumApps
)s_prior_visit,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-FEB-2009',1,0)*v2.NumApps) s_visit_month_1,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-MAR-2009',1,0)*v2.NumApps) s_visit_month_2,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-APR-2009',1,0)*v2.NumApps) s_visit_month_3,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-MAY-2009',1,0)*v2.NumApps) s_visit_month_4,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-JUN-2009',1,0)*v2.NumApps) s_visit_month_5,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-JUL-2009',1,0)*v2.NumApps) s_visit_month_6,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-AUG-2009',1,0)*v2.NumApps) s_visit_month_7,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-SEP-2009',1,0)*v2.NumApps) s_visit_month_8,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-OCT-2009',1,0)*v2.NumApps) s_visit_month_9,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-NOV-2009',1,0)*v2.NumApps) s_visit_month_10,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-DEC-2009',1,0)*v2.NumApps) s_visit_month_11,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-JAN-2010',1,0)*v2.NumApps) s_visit_month_12,
SUM(DECODE(
          SIGN(TO_DATE('31-JAN-2010'||' 23:59:59','DD-MON-YYYY HH24:MI:SS')-v2.compl_date),
          -1,1,
          0
   )*v2.NumApps
) s_post_visit,
SUM(DECODE(v2.compl_date,NULL,1,0)*v2.NumApps) s_no_visit
FROM
(
SELECT
    /*+PARALLEL(svco1,4)*/
    v.uabscon_cancellation_date,
    v.NumApps,
    svco1.max_ucbsvco_completion_date compl_date
FROM
     (  
SELECT
    
    /*+PARALLEL(a,4)*/
   
    a.uabscon_prem_code,
    a.uabscon_cust_code,
    a.uabscon_cancellation_date,
    COUNT(*) NumApps
FROM
    uimsmgr.uabscon a,
    uimsmgr.uarserq b,
    (
    SELECT
        /*+PARALLEL(b,4)*/
        b.utrjapp_srvc_code,
        b.utrjapp_styp_code,
        b.utrjapp_visit_months,
        b.utrjapp_li_ind,
        c.utvsrvc_guarantee
    FROM
        uimsmgr.utrjapp b,
        uimsmgr.utvsrvc c
    WHERE
            c.utvsrvc_bus_sector_id = 1
        AND b.utrjapp_srvc_code = c.utvsrvc_code
        AND b.utrjapp_visit_months <> 0
        AND rownum<100
    ) japp1
    WHERE
        b.uarserq_cust_code = a.uabscon_cust_code 
    AND b.uarserq_prem_code = a.uabscon_prem_code
    AND a.uabscon_status_ind = 'C'
    AND a.uabscon_cancellation_date BETWEEN
            TO_DATE('01-FEB-2009') 
        AND TO_DATE('31-JAN-2010'||' 23:59:59','DD-MON-YYYY HH24:MI:SS')
    AND b.uarserq_srvc_code = japp1.utrjapp_srvc_code
    AND b.uarserq_styp_code = japp1.utrjapp_styp_code
    AND b.uarserq_serv_num > 0
-- No other active CONTRACT at the PREMISES
    AND NOT EXISTS (
        SELECT 
             'X'
        FROM
            uimsmgr.uabscon f 
        WHERE
                f.uabscon_prem_code = a.uabscon_prem_code 
            AND f.uabscon_status_ind != 'P'
            AND f.uabscon_status_ind != 'C'
            AND rownum<100
        )
-- Most recent quote
    AND b.uarserq_quote_number = (
            SELECT MAX(t.uabletq_quote_number) 
            FROM
                uimsmgr.uabletq t  
            WHERE
                    t.uabletq_cust_code =  a.uabscon_cust_code 
                AND t.uabletq_prem_code =  a.uabscon_prem_code
               AND rownum<100
            )
            AND rownum<100
GROUP BY
    a.uabscon_prem_code,
    a.uabscon_cust_code,
    a.uabscon_cancellation_date
      ) v,
    (
    SELECT /*+PARALLEL(svco2,4)*/
        svco2.ucbsvco_prem_code,
        svco2.ucbsvco_cust_code,
        MAX(svco2.ucbsvco_completion_date) max_ucbsvco_completion_date
    FROM
        uimsmgr.ucbsvco svco2
    WHERE
            svco2.ucbsvco_stus_code = 'C'
        AND svco2.ucbsvco_sotp_code IN ('AS','FV')
        AND rownum<100
    GROUP BY
        svco2.ucbsvco_prem_code,
        svco2.ucbsvco_cust_code
    ) svco1
WHERE
        svco1.ucbsvco_cust_code (+) = v.uabscon_cust_code
    AND svco1.ucbsvco_prem_code (+) = v.uabscon_prem_code
    AND rownum<100
     
) v2
GROUP BY
    TO_CHAR(v2.uabscon_cancellation_date,'YYYYMM'),
    TO_CHAR(v2.uabscon_cancellation_date,'MON-YYYY')