Hi Guru's,
I have been running below query like 6 months now suddenly its performing poor. Its simple doing SUM and using GROUP by on more than 10 columns.
Can you please suggest how i can rewrite the query or any performance.
There are only two tables joins on different column and also using union all.
{
SELECT PRODUCT_YEAR,
BRAND,
COMPANY,
PROD,
REC,
FRQ,
MULTI_SEG,
TRV_STS,
EMAILABLE_FLG,
MAILABLE_FLG,
LAST_COMPANY_TRAVELLED,
GCCLR_EVER_NEVER,
GCCLS_EVER_NEVER,
OAT_EVER_NEVER,
GCT_EVER_NEVER,
ever_never,
RS_EVER,
SS_EVER,
TPC_CAT,
TPC_CAT_GRP_1,
TPC_CAT_GRP_5,
TPC_REC_RANK,
TPC_REC_CAT_KEY,
-- TPC_REC_LEVEL,
tpc_rank,
-- tpc_rank_key,
TPC_NBR,
PROD_COUNT,
GCC_FY_RANK,
TPC_LEVEL,
UNV,
HHR,
HHR_FIELD,
HHR_FIELD_LW,
HHR_FIELD_L4W,
HHR_FIELD_P4W,
GCC_BK_FLG,
TRIP_CNT,
PAX,
GP,
ADV,
NP,
PAX_L4WK,
GP_L4WK,
ADV_L4WK,
NP_L4WK,
PAX_P4WK,
GP_P4WK,
ADV_P4WK,
NP_P4WK,
PAX_LWK,
GP_LWK,
ADV_LWK,
NP_LWK,
PAX_PWK,
GP_PWK,
ADV_PWK,
NP_PWK,
/*------------------------*/
PAX_Q1,
GP_Q1,
PAX_Q2,
GP_Q2,
PAX_Q3,
GP_Q3,
PAX_Q4,
GP_Q4,
---------------
PAX_JAN_APR,
GP_JAN_APR,
----------------
PAX_Q1_L4WK,
GP_Q1_L4WK,
PAX_Q2_L4WK,
GP_Q2_L4WK,
PAX_Q3_L4WK,
GP_Q3_L4WK,
PAX_Q4_L4WK,
GP_Q4_L4WK,
---------------
PAX_JAN_APR_L4WK,
GP_JAN_APR_L4WK,
----------------
PAX_Q1_P4WK,
GP_Q1_P4WK,
PAX_Q2_P4WK,
GP_Q2_P4WK,
PAX_Q3_P4WK,
GP_Q3_P4WK,
PAX_Q4_P4WK,
GP_Q4_P4WK,
---------------
PAX_JAN_APR_P4WK,
GP_JAN_APR_P4WK,
----------------
PAX_Q1_LWK,
GP_Q1_LWK,
PAX_Q2_LWK,
GP_Q2_LWK,
PAX_Q3_LWK,
GP_Q3_LWK,
PAX_Q4_LWK,
GP_Q4_LWK,
---------------
PAX_JAN_APR_LWK,
GP_JAN_APR_LWK,
----------------
PAX_Q1_PWK,
GP_Q1_PWK,
PAX_Q2_PWK,
GP_Q2_PWK,
PAX_Q3_PWK,
GP_Q3_PWK,
PAX_Q4_PWK,
GP_Q4_PWK,
---------------
PAX_JAN_APR_PWK,
GP_JAN_APR_PWK,
----------------
VA_PAX,
VA_GP,
VA_PAX_L4WK,
VA_GP_L4WK,
VA_PAX_P4WK,
VA_GP_P4WK,
VA_PAX_LWK,
VA_GP_LWK,
VA_PAX_PWK,
VA_GP_PWK,
/*------------------------*/
VA_PAX_Q1,
VA_GP_Q1,
VA_PAX_Q2,
VA_GP_Q2,
VA_PAX_Q3,
VA_GP_Q3,
VA_PAX_Q4,
VA_GP_Q4,
---------------
VA_PAX_JAN_APR,
VA_GP_JAN_APR,
----------------
VA_PAX_Q1_L4WK,
VA_GP_Q1_L4WK,
VA_PAX_Q2_L4WK,
VA_GP_Q2_L4WK,
VA_PAX_Q3_L4WK,
VA_GP_Q3_L4WK,
VA_PAX_Q4_L4WK,
VA_GP_Q4_L4WK,
---------------
VA_PAX_JAN_APR_L4WK,
VA_GP_JAN_APR_L4WK,
----------------
VA_PAX_Q1_P4WK,
VA_GP_Q1_P4WK,
VA_PAX_Q2_P4WK,
VA_GP_Q2_P4WK,
VA_PAX_Q3_P4WK,
VA_GP_Q3_P4WK,
VA_PAX_Q4_P4WK,
VA_GP_Q4_P4WK,
---------------
VA_PAX_JAN_APR_P4WK,
VA_GP_JAN_APR_P4WK,
----------------
VA_PAX_Q1_LWK,
VA_GP_Q1_LWK,
VA_PAX_Q2_LWK,
VA_GP_Q2_LWK,
VA_PAX_Q3_LWK,
VA_GP_Q3_LWK,
VA_PAX_Q4_LWK,
VA_GP_Q4_LWK,
---------------
VA_PAX_JAN_APR_LWK,
VA_GP_JAN_APR_LWK,
----------------
VA_PAX_Q1_PWK,
VA_GP_Q1_PWK,
VA_PAX_Q2_PWK,
VA_GP_Q2_PWK,
VA_PAX_Q3_PWK,
VA_GP_Q3_PWK,
VA_PAX_Q4_PWK,
VA_GP_Q4_PWK,
---------------
VA_PAX_JAN_APR_PWK,
VA_GP_JAN_APR_PWK,
----------------
PENDING_FLAG,
SYSDATE AS DATA_DATE
FROM ( SELECT AL1.PRODUCT_YEAR,
'GCC' BRAND,
'GCC' COMPANY,
'GCC' PROD,
REC,
FRQ,
MULTI_SEG,
TRV_STS,
EMAILABLE_FLG,
MAILABLE_FLG,
LAST_COMPANY_TRAVELLED,
GCCLR_EVER_NEVER,
GCCLS_EVER_NEVER,
OAT_EVER_NEVER,
GCT_EVER_NEVER,
ever_never,
RS_EVER,
SS_EVER,
AL2.TPC_CAT AS TPC_CAT,
AL2.TPC_CAT_GRP_1 AS TPC_CAT_GRP_1,
AL2.TPC_CAT_GRP_5 AS TPC_CAT_GRP_5,
--------------------------------------------------
TPC_REC_CAT_GCC_RANK AS TPC_REC_RANK,
TPC_REC_CAT_GCC_KEY AS TPC_REC_CAT_KEY,
-- TPC_REC_LEVEL_GCC_DESC AS TPC_REC_LEVEL,
-- TPC_cust_GCC_RANK as tpc_Rank,
case when to_number( TPC_cust_GCC_RANK_key) = 999 then 999 when to_number( TPC_cust_GCC_RANK_key) >= 10 then 10 else to_number( TPC_cust_GCC_RANK_key) end as tpc_rank,
-----------------------------------
0 AS TPC_NBR,
SUM (CASE WHEN AL2.TPC_CAT_BUCKET >= 3 AND AL3.TPC_CAT_BUCKET >= AL2.TPC_CAT_BUCKET THEN 1 ELSE 0 END) AS PROD_COUNT,
0 GCC_FY_RANK,
TPC_LEVEL_GCC_DESC AS TPC_LEVEL,
PENDING_FLAG,
COUNT (DISTINCT CUST_NUM) UNV,
COUNT (DISTINCT CASE WHEN BK_FLG = 'Y' THEN CUST_NUM ELSE NULL END) HHR,
GCC_BK_FLG,
SUM (NVL (TRIP_CNT, 0)) TRIP_CNT,
SUM (NVL (PAX, 0)) PAX,
SUM (NVL (HHR_CORP, 0)) HHR_FIELD,
SUM (NVL (HHR_CORP_LW, 0)) HHR_FIELD_LW,
SUM (NVL (HHR_CORP_L4W, 0)) HHR_FIELD_L4W,
SUM (NVL (HHR_CORP_P4W, 0)) HHR_FIELD_P4W,
SUM (NVL (GP, 0)) GP,
SUM (NVL (ADV, 0)) ADV,
SUM (NVL (GP, 0)) - SUM (NVL (ADV, 0)) NP,
SUM (NVL (PAX_L4W, 0)) PAX_L4WK,
SUM (NVL (GP_L4W, 0)) GP_L4WK,
SUM (NVL (ADV_L4W, 0)) ADV_L4WK,
SUM (NVL (GP_L4W, 0)) - SUM (NVL (ADV_L4W, 0)) NP_L4WK,
SUM (NVL (PAX_P4W, 0)) PAX_P4WK,
SUM (NVL (GP_P4W, 0)) GP_P4WK,
SUM (NVL (ADV_P4W, 0)) ADV_P4WK,
SUM (NVL (GP_P4W, 0)) - SUM (NVL (ADV_P4W, 0)) NP_P4WK,
SUM (PAX_LW) PAX_LWK,
SUM (NVL (GP_LW, 0)) GP_LWK,
SUM (NVL (ADV_LW, 0)) ADV_LWK,
SUM (NVL (GP_LW, 0)) - SUM (NVL (ADV_LW, 0)) NP_LWK,
SUM (NVL (PAX_PW, 0)) PAX_PWK,
SUM (NVL (GP_PW, 0)) GP_PWK,
SUM (NVL (ADV_PW, 0)) ADV_PWK,
SUM (NVL (GP_PW, 0)) - SUM (NVL (ADV_PW, 0)) NP_PWK,
/*------------------------*/
SUM (NVL (PAX_Q1, 0)) AS PAX_Q1,
SUM (NVL (GP_Q1, 0)) AS GP_Q1,
SUM (NVL (PAX_Q2, 0)) AS PAX_Q2,
SUM (NVL (GP_Q2, 0)) AS GP_Q2,
SUM (NVL (PAX_Q3, 0)) AS PAX_Q3,
SUM (NVL (GP_Q3, 0)) AS GP_Q3,
SUM (NVL (PAX_Q4, 0)) AS PAX_Q4,
SUM (NVL (GP_Q4, 0)) AS GP_Q4,
---------------------
SUM (NVL (PAX_JAN_APR, 0)) AS PAX_JAN_APR,
SUM (NVL (GP_JAN_APR, 0)) AS GP_JAN_APR,
----------------------
SUM (NVL (PAX_Q1_L4W, 0)) AS PAX_Q1_L4WK,
SUM (NVL (GP_Q1_L4W, 0)) AS GP_Q1_L4WK,
SUM (NVL (PAX_Q2_L4W, 0)) AS PAX_Q2_L4WK,
SUM (NVL (GP_Q2_L4W, 0)) AS GP_Q2_L4WK,
SUM (NVL (PAX_Q3_L4W, 0)) AS PAX_Q3_L4WK,
SUM (NVL (GP_Q3_L4W, 0)) AS GP_Q3_L4WK,
SUM (NVL (PAX_Q4_L4W, 0)) AS PAX_Q4_L4WK,
SUM (NVL (GP_Q4_L4W, 0)) AS GP_Q4_L4WK,
---------------------
SUM (NVL (PAX_JAN_APR_L4W, 0)) AS PAX_JAN_APR_L4WK,
SUM (NVL (GP_JAN_APR_L4W, 0)) AS GP_JAN_APR_L4WK,
----------------------
SUM (NVL (PAX_Q1_P4W, 0)) AS PAX_Q1_P4WK,
SUM (NVL (GP_Q1_P4W, 0)) AS GP_Q1_P4WK,
SUM (NVL (PAX_Q2_P4W, 0)) AS PAX_Q2_P4WK,
SUM (NVL (GP_Q2_P4W, 0)) AS GP_Q2_P4WK,
SUM (NVL (PAX_Q3_P4W, 0)) AS PAX_Q3_P4WK,
SUM (NVL (GP_Q3_P4W, 0)) AS GP_Q3_P4WK,
SUM (NVL (PAX_Q4_P4W, 0)) AS PAX_Q4_P4WK,
SUM (NVL (GP_Q4_P4W, 0)) AS GP_Q4_P4WK,
---------------------
SUM (NVL (PAX_JAN_APR_P4W, 0)) AS PAX_JAN_APR_P4WK,
SUM (NVL (GP_JAN_APR_P4W, 0)) AS GP_JAN_APR_P4WK,
----------------------
SUM (NVL (PAX_Q1_LW, 0)) AS PAX_Q1_LWK,
SUM (NVL (GP_Q1_LW, 0)) AS GP_Q1_LWK,
SUM (NVL (PAX_Q2_LW, 0)) AS PAX_Q2_LWK,
SUM (NVL (GP_Q2_LW, 0)) AS GP_Q2_LWK,
SUM (NVL (PAX_Q3_LW, 0)) AS PAX_Q3_LWK,
SUM (NVL (GP_Q3_LW, 0)) AS GP_Q3_LWK,
SUM (NVL (PAX_Q4_LW, 0)) AS PAX_Q4_LWK,
SUM (NVL (GP_Q4_LW, 0)) AS GP_Q4_LWK,
---------------------
SUM (NVL (PAX_JAN_APR_LW, 0)) AS PAX_JAN_APR_LWK,
SUM (NVL (GP_JAN_APR_LW, 0)) AS GP_JAN_APR_LWK,
----------------------
SUM (NVL (PAX_Q1_PW, 0)) AS PAX_Q1_PWK,
SUM (NVL (GP_Q1_PW, 0)) AS GP_Q1_PWK,
SUM (NVL (PAX_Q2_PW, 0)) AS PAX_Q2_PWK,
SUM (NVL (GP_Q2_PW, 0)) AS GP_Q2_PWK,
SUM (NVL (PAX_Q3_PW, 0)) AS PAX_Q3_PWK,
SUM (NVL (GP_Q3_PW, 0)) AS GP_Q3_PWK,
SUM (NVL (PAX_Q4_PW, 0)) AS PAX_Q4_PWK,
SUM (NVL (GP_Q4_PW, 0)) AS GP_Q4_PWK,
---------------------
SUM (NVL (PAX_JAN_APR_PW, 0)) AS PAX_JAN_APR_PWK,
SUM (NVL (GP_JAN_APR_PW, 0)) AS GP_JAN_APR_PWK,
----------------------
---------------------------------
SUM (NVL (VA_PAX, 0)) VA_PAX,
SUM (NVL (VA_GP, 0)) VA_GP,
SUM (NVL (VA_PAX_L4W, 0)) VA_PAX_L4WK,
SUM (NVL (VA_GP_L4W, 0)) VA_GP_L4WK,
SUM (NVL (VA_PAX_P4W, 0)) VA_PAX_P4WK,
SUM (NVL (VA_GP_P4W, 0)) VA_GP_P4WK,
SUM (VA_PAX_LW) VA_PAX_LWK,
SUM (NVL (VA_GP_LW, 0)) VA_GP_LWK,
SUM (NVL (VA_PAX_PW, 0)) VA_PAX_PWK,
SUM (NVL (VA_GP_PW, 0)) VA_GP_PWK,
/*------------------------*/
SUM (NVL (VA_PAX_Q1, 0)) AS VA_PAX_Q1,
SUM (NVL (VA_GP_Q1, 0)) AS VA_GP_Q1,
SUM (NVL (VA_PAX_Q2, 0)) AS VA_PAX_Q2,
SUM (NVL (VA_GP_Q2, 0)) AS VA_GP_Q2,
SUM (NVL (VA_PAX_Q3, 0)) AS VA_PAX_Q3,
SUM (NVL (VA_GP_Q3, 0)) AS VA_GP_Q3,
SUM (NVL (VA_PAX_Q4, 0)) AS VA_PAX_Q4,
SUM (NVL (VA_GP_Q4, 0)) AS VA_GP_Q4,
---------------------
SUM (NVL (VA_PAX_JAN_APR, 0)) AS VA_PAX_JAN_APR,
SUM (NVL (VA_GP_JAN_APR, 0)) AS VA_GP_JAN_APR,
----------------------
SUM (NVL (VA_PAX_Q1_L4W, 0)) AS VA_PAX_Q1_L4WK,
SUM (NVL (VA_GP_Q1_L4W, 0)) AS VA_GP_Q1_L4WK,
SUM (NVL (VA_PAX_Q2_L4W, 0)) AS VA_PAX_Q2_L4WK,
SUM (NVL (VA_GP_Q2_L4W, 0)) AS VA_GP_Q2_L4WK,
SUM (NVL (VA_PAX_Q3_L4W, 0)) AS VA_PAX_Q3_L4WK,
SUM (NVL (VA_GP_Q3_L4W, 0)) AS VA_GP_Q3_L4WK,
SUM (NVL (VA_PAX_Q4_L4W, 0)) AS VA_PAX_Q4_L4WK,
SUM (NVL (VA_GP_Q4_L4W, 0)) AS VA_GP_Q4_L4WK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_L4W, 0)) AS VA_PAX_JAN_APR_L4WK,
SUM (NVL (VA_GP_JAN_APR_L4W, 0)) AS VA_GP_JAN_APR_L4WK,
----------------------
SUM (NVL (VA_PAX_Q1_P4W, 0)) AS VA_PAX_Q1_P4WK,
SUM (NVL (VA_GP_Q1_P4W, 0)) AS VA_GP_Q1_P4WK,
SUM (NVL (VA_PAX_Q2_P4W, 0)) AS VA_PAX_Q2_P4WK,
SUM (NVL (VA_GP_Q2_P4W, 0)) AS VA_GP_Q2_P4WK,
SUM (NVL (VA_PAX_Q3_P4W, 0)) AS VA_PAX_Q3_P4WK,
SUM (NVL (VA_GP_Q3_P4W, 0)) AS VA_GP_Q3_P4WK,
SUM (NVL (VA_PAX_Q4_P4W, 0)) AS VA_PAX_Q4_P4WK,
SUM (NVL (VA_GP_Q4_P4W, 0)) AS VA_GP_Q4_P4WK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_P4W, 0)) AS VA_PAX_JAN_APR_P4WK,
SUM (NVL (VA_GP_JAN_APR_P4W, 0)) AS VA_GP_JAN_APR_P4WK,
----------------------
SUM (NVL (VA_PAX_Q1_LW, 0)) AS VA_PAX_Q1_LWK,
SUM (NVL (VA_GP_Q1_LW, 0)) AS VA_GP_Q1_LWK,
SUM (NVL (VA_PAX_Q2_LW, 0)) AS VA_PAX_Q2_LWK,
SUM (NVL (VA_GP_Q2_LW, 0)) AS VA_GP_Q2_LWK,
SUM (NVL (VA_PAX_Q3_LW, 0)) AS VA_PAX_Q3_LWK,
SUM (NVL (VA_GP_Q3_LW, 0)) AS VA_GP_Q3_LWK,
SUM (NVL (VA_PAX_Q4_LW, 0)) AS VA_PAX_Q4_LWK,
SUM (NVL (VA_GP_Q4_LW, 0)) AS VA_GP_Q4_LWK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_LW, 0)) AS VA_PAX_JAN_APR_LWK,
SUM (NVL (VA_GP_JAN_APR_LW, 0)) AS VA_GP_JAN_APR_LWK,
----------------------
SUM (NVL (VA_PAX_Q1_PW, 0)) AS VA_PAX_Q1_PWK,
SUM (NVL (VA_GP_Q1_PW, 0)) AS VA_GP_Q1_PWK,
SUM (NVL (VA_PAX_Q2_PW, 0)) AS VA_PAX_Q2_PWK,
SUM (NVL (VA_GP_Q2_PW, 0)) AS VA_GP_Q2_PWK,
SUM (NVL (VA_PAX_Q3_PW, 0)) AS VA_PAX_Q3_PWK,
SUM (NVL (VA_GP_Q3_PW, 0)) AS VA_GP_Q3_PWK,
SUM (NVL (VA_PAX_Q4_PW, 0)) AS VA_PAX_Q4_PWK,
SUM (NVL (VA_GP_Q4_PW, 0)) AS VA_GP_Q4_PWK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_PW, 0)) AS VA_PAX_JAN_APR_PWK,
SUM (NVL (VA_GP_JAN_APR_PW, 0)) AS VA_GP_JAN_APR_PWK
----------------------
FROM AW.jl_CUST_PROD_DTL_2017_YTD_rk AL1
LEFT JOIN AW.CM_CUST_PROD_TPC_CAT_DIM AL2 ON AL1.TPC_CAT_GCC_KEY = AL2.TPC_CAT_KEY
LEFT JOIN AW.CM_CUST_PROD_TPC_CAT_DIM AL3 ON AL1.TPC_CAT_PROD_KEY = AL3.TPC_CAT_KEY
-- WHERE cust_num = 16295
GROUP BY AL1.PRODUCT_YEAR,
REC,
FRQ,
MULTI_SEG,
TRV_STS,
EMAILABLE_FLG,
MAILABLE_FLG,
LAST_COMPANY_TRAVELLED,
GCCLR_EVER_NEVER,
GCCLS_EVER_NEVER,
OAT_EVER_NEVER,
GCT_EVER_NEVER,
ever_never,
RS_EVER,
SS_EVER,
AL2.TPC_CAT,
AL2.TPC_CAT_GRP_1,
AL2.TPC_CAT_GRP_5,
GCC_BK_FLG,
TPC_LEVEL_GCC_DESC,
TPC_REC_CAT_GCC_RANK,
TPC_REC_CAT_GCC_KEY,
case when to_number( TPC_cust_GCC_RANK_key) = 999 then 999 when to_number( TPC_cust_GCC_RANK_key) >= 10 then 10 else to_number( TPC_cust_GCC_RANK_key) end,
PENDING_FLAG
-- TPC_REC_LEVEL_GCC_DESC
UNION ALL
---------------
-- Brand
--------------------
SELECT AL1.PRODUCT_YEAR,
AL1.BRAND,
AL1.BRAND COMPANY,
AL1.BRAND PROD,
REC,
FRQ,
MULTI_SEG,
TRV_STS,
EMAILABLE_FLG,
MAILABLE_FLG,
LAST_COMPANY_TRAVELLED,
GCCLR_EVER_NEVER,
GCCLS_EVER_NEVER,
OAT_EVER_NEVER,
GCT_EVER_NEVER,
ever_never,
RS_EVER,
SS_EVER,
AL2.TPC_CAT AS TPC_CAT,
AL2.TPC_CAT_GRP_1 AS TPC_CAT_GRP_1,
AL2.TPC_CAT_GRP_5 AS TPC_CAT_GRP_5,
TPC_REC_CAT_BRAND_RANK AS TPC_REC_RANK,
TPC_REC_CAT_BRAND_KEY AS TPC_REC_CAT_KEY,
-- TPC_REC_LEVEL_BRAND_DESC AS TPC_REC_LEVEL,
-- TPC_cust_brand_RANK as tpc_Rank,
case when to_number( TPC_cust_brand_RANK_key) = 999 then 999 when to_number( TPC_cust_brand_RANK_key) >= 10 then 10 else to_number( TPC_cust_brand_RANK_key) end as tpc_Rank,
-- TPC_cust_brand_RANK_key as tpc_rank_key,
0 TPC_NBR,
SUM (CASE WHEN AL2.TPC_CAT_BUCKET >= 3 AND AL3.TPC_CAT_BUCKET >= AL2.TPC_CAT_BUCKET THEN 1 ELSE 0 END) AS PROD_COUNT,
0 GCC_FY_RANK,
TPC_LEVEL_BRAND_DESC,
PENDING_FLAG,
COUNT (DISTINCT CUST_NUM) UNV,
COUNT (DISTINCT CASE WHEN BK_FLG = 'Y' THEN CUST_NUM ELSE NULL END) HHR,
GCC_BK_FLG,
SUM (NVL (TRIP_CNT, 0)) TRIP_CNT,
SUM (NVL (PAX, 0)) PAX,
SUM (NVL (HHR_BRAND, 0)) HHR_FIELD,
SUM (NVL (HHR_BRAND_LW, 0)) HHR_FIELD_LW,
SUM (NVL (HHR_BRAND_L4W, 0)) HHR_FIELD_L4W,
SUM (NVL (HHR_BRAND_P4W, 0)) HHR_FIELD_P4W,
SUM (NVL (GP, 0)) GP,
SUM (NVL (ADV, 0)) ADV,
SUM (NVL (GP, 0)) - SUM (NVL (ADV, 0)) NP,
SUM (NVL (PAX_L4W, 0)) PAX_L4WK,
SUM (NVL (GP_L4W, 0)) GP_L4WK,
SUM (NVL (ADV_L4W, 0)) ADV_L4WK,
SUM (NVL (GP_L4W, 0)) - SUM (NVL (ADV_L4W, 0)) NP_L4WK,
SUM (NVL (PAX_P4W, 0)) PAX_P4WK,
SUM (NVL (GP_P4W, 0)) GP_P4WK,
SUM (NVL (ADV_P4W, 0)) ADV_P4WK,
SUM (NVL (GP_P4W, 0)) - SUM (NVL (ADV_P4W, 0)) NP_P4WK,
SUM (PAX_LW) PAX_LWK,
SUM (NVL (GP_LW, 0)) GP_LWK,
SUM (NVL (ADV_LW, 0)) ADV_LWK,
SUM (NVL (GP_LW, 0)) - SUM (NVL (ADV_LW, 0)) NP_LWK,
SUM (NVL (PAX_PW, 0)) PAX_PWK,
SUM (NVL (GP_PW, 0)) GP_PWK,
SUM (NVL (ADV_PW, 0)) ADV_PWK,
SUM (NVL (GP_PW, 0)) - SUM (NVL (ADV_PW, 0)) NP_PWK,
/*------------------------*/
SUM (NVL (PAX_Q1, 0)) AS PAX_Q1,
SUM (NVL (GP_Q1, 0)) AS GP_Q1,
SUM (NVL (PAX_Q2, 0)) AS PAX_Q2,
SUM (NVL (GP_Q2, 0)) AS GP_Q2,
SUM (NVL (PAX_Q3, 0)) AS PAX_Q3,
SUM (NVL (GP_Q3, 0)) AS GP_Q3,
SUM (NVL (PAX_Q4, 0)) AS PAX_Q4,
SUM (NVL (GP_Q4, 0)) AS GP_Q4,
---------------------
SUM (NVL (PAX_JAN_APR, 0)) AS PAX_JAN_APR,
SUM (NVL (GP_JAN_APR, 0)) AS GP_JAN_APR,
----------------------
SUM (NVL (PAX_Q1_L4W, 0)) AS PAX_Q1_L4WK,
SUM (NVL (GP_Q1_L4W, 0)) AS GP_Q1_L4WK,
SUM (NVL (PAX_Q2_L4W, 0)) AS PAX_Q2_L4WK,
SUM (NVL (GP_Q2_L4W, 0)) AS GP_Q2_L4WK,
SUM (NVL (PAX_Q3_L4W, 0)) AS PAX_Q3_L4WK,
SUM (NVL (GP_Q3_L4W, 0)) AS GP_Q3_L4WK,
SUM (NVL (PAX_Q4_L4W, 0)) AS PAX_Q4_L4WK,
SUM (NVL (GP_Q4_L4W, 0)) AS GP_Q4_L4WK,
---------------------
SUM (NVL (PAX_JAN_APR_L4W, 0)) AS PAX_JAN_APR_L4Wk,
SUM (NVL (GP_JAN_APR_L4W, 0)) AS GP_JAN_APR_L4Wk,
----------------------
SUM (NVL (PAX_Q1_P4W, 0)) AS PAX_Q1_P4WK,
SUM (NVL (GP_Q1_P4W, 0)) AS GP_Q1_P4WK,
SUM (NVL (PAX_Q2_P4W, 0)) AS PAX_Q2_P4WK,
SUM (NVL (GP_Q2_P4W, 0)) AS GP_Q2_P4WK,
SUM (NVL (PAX_Q3_P4W, 0)) AS PAX_Q3_P4WK,
SUM (NVL (GP_Q3_P4W, 0)) AS GP_Q3_P4WK,
SUM (NVL (PAX_Q4_P4W, 0)) AS PAX_Q4_P4WK,
SUM (NVL (GP_Q4_P4W, 0)) AS GP_Q4_P4WK,
---------------------
SUM (NVL (PAX_JAN_APR_P4W, 0)) AS PAX_JAN_APR_P4WK,
SUM (NVL (GP_JAN_APR_P4W, 0)) AS GP_JAN_APR_P4WK,
----------------------
SUM (NVL (PAX_Q1_LW, 0)) AS PAX_Q1_LWK,
SUM (NVL (GP_Q1_LW, 0)) AS GP_Q1_LWK,
SUM (NVL (PAX_Q2_LW, 0)) AS PAX_Q2_LWK,
SUM (NVL (GP_Q2_LW, 0)) AS GP_Q2_LWK,
SUM (NVL (PAX_Q3_LW, 0)) AS PAX_Q3_LWK,
SUM (NVL (GP_Q3_LW, 0)) AS GP_Q3_LWK,
SUM (NVL (PAX_Q4_LW, 0)) AS PAX_Q4_LWK,
SUM (NVL (GP_Q4_LW, 0)) AS GP_Q4_LWK,
---------------------
SUM (NVL (PAX_JAN_APR_LW, 0)) AS PAX_JAN_APR_LWK,
SUM (NVL (GP_JAN_APR_LW, 0)) AS GP_JAN_APR_LWK,
----------------------
SUM (NVL (PAX_Q1_PW, 0)) AS PAX_Q1_PWK,
SUM (NVL (GP_Q1_PW, 0)) AS GP_Q1_PWK,
SUM (NVL (PAX_Q2_PW, 0)) AS PAX_Q2_PWK,
SUM (NVL (GP_Q2_PW, 0)) AS GP_Q2_PWK,
SUM (NVL (PAX_Q3_PW, 0)) AS PAX_Q3_PWK,
SUM (NVL (GP_Q3_PW, 0)) AS GP_Q3_PWK,
SUM (NVL (PAX_Q4_PW, 0)) AS PAX_Q4_PWK,
SUM (NVL (GP_Q4_PW, 0)) AS GP_Q4_PWK,
---------------------
SUM (NVL (PAX_JAN_APR_PW, 0)) AS PAX_JAN_APR_PWK,
SUM (NVL (GP_JAN_APR_PW, 0)) AS GP_JAN_APR_PWK,
----------------------
---------------------------------
SUM (NVL (VA_PAX, 0)) VA_PAX,
SUM (NVL (VA_GP, 0)) VA_GP,
SUM (NVL (VA_PAX_L4W, 0)) VA_PAX_L4WK,
SUM (NVL (VA_GP_L4W, 0)) VA_GP_L4WK,
SUM (NVL (VA_PAX_P4W, 0)) VA_PAX_P4WK,
SUM (NVL (VA_GP_P4W, 0)) VA_GP_P4WK,
SUM (VA_PAX_LW) VA_PAX_LWK,
SUM (NVL (VA_GP_LW, 0)) VA_GP_LWK,
SUM (NVL (VA_PAX_PW, 0)) VA_PAX_PWK,
SUM (NVL (VA_GP_PW, 0)) VA_GP_PWK,
/*------------------------*/
SUM (NVL (VA_PAX_Q1, 0)) AS VA_PAX_Q1,
SUM (NVL (VA_GP_Q1, 0)) AS VA_GP_Q1,
SUM (NVL (VA_PAX_Q2, 0)) AS VA_PAX_Q2,
SUM (NVL (VA_GP_Q2, 0)) AS VA_GP_Q2,
SUM (NVL (VA_PAX_Q3, 0)) AS VA_PAX_Q3,
SUM (NVL (VA_GP_Q3, 0)) AS VA_GP_Q3,
SUM (NVL (VA_PAX_Q4, 0)) AS VA_PAX_Q4,
SUM (NVL (VA_GP_Q4, 0)) AS VA_GP_Q4,
---------------------
SUM (NVL (VA_PAX_JAN_APR, 0)) AS VA_PAX_JAN_APR,
SUM (NVL (VA_GP_JAN_APR, 0)) AS VA_GP_JAN_APR,
----------------------
SUM (NVL (VA_PAX_Q1_L4W, 0)) AS VA_PAX_Q1_L4WK,
SUM (NVL (VA_GP_Q1_L4W, 0)) AS VA_GP_Q1_L4WK,
SUM (NVL (VA_PAX_Q2_L4W, 0)) AS VA_PAX_Q2_L4WK,
SUM (NVL (VA_GP_Q2_L4W, 0)) AS VA_GP_Q2_L4WK,
SUM (NVL (VA_PAX_Q3_L4W, 0)) AS VA_PAX_Q3_L4WK,
SUM (NVL (VA_GP_Q3_L4W, 0)) AS VA_GP_Q3_L4WK,
SUM (NVL (VA_PAX_Q4_L4W, 0)) AS VA_PAX_Q4_L4WK,
SUM (NVL (VA_GP_Q4_L4W, 0)) AS VA_GP_Q4_L4WK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_L4W, 0)) AS VA_PAX_JAN_APR_L4WK,
SUM (NVL (VA_GP_JAN_APR_L4W, 0)) AS VA_GP_JAN_APR_L4WK,
----------------------
SUM (NVL (VA_PAX_Q1_P4W, 0)) AS VA_PAX_Q1_P4WK,
SUM (NVL (VA_GP_Q1_P4W, 0)) AS VA_GP_Q1_P4WK,
SUM (NVL (VA_PAX_Q2_P4W, 0)) AS VA_PAX_Q2_P4WK,
SUM (NVL (VA_GP_Q2_P4W, 0)) AS VA_GP_Q2_P4WK,
SUM (NVL (VA_PAX_Q3_P4W, 0)) AS VA_PAX_Q3_P4WK,
SUM (NVL (VA_GP_Q3_P4W, 0)) AS VA_GP_Q3_P4WK,
SUM (NVL (VA_PAX_Q4_P4W, 0)) AS VA_PAX_Q4_P4WK,
SUM (NVL (VA_GP_Q4_P4W, 0)) AS VA_GP_Q4_P4WK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_P4W, 0)) AS VA_PAX_JAN_APR_P4WK,
SUM (NVL (VA_GP_JAN_APR_P4W, 0)) AS VA_GP_JAN_APR_P4WK,
----------------------
SUM (NVL (VA_PAX_Q1_LW, 0)) AS VA_PAX_Q1_LWK,
SUM (NVL (VA_GP_Q1_LW, 0)) AS VA_GP_Q1_LWK,
SUM (NVL (VA_PAX_Q2_LW, 0)) AS VA_PAX_Q2_LWK,
SUM (NVL (VA_GP_Q2_LW, 0)) AS VA_GP_Q2_LWK,
SUM (NVL (VA_PAX_Q3_LW, 0)) AS VA_PAX_Q3_LWK,
SUM (NVL (VA_GP_Q3_LW, 0)) AS VA_GP_Q3_LWK,
SUM (NVL (VA_PAX_Q4_LW, 0)) AS VA_PAX_Q4_LWK,
SUM (NVL (VA_GP_Q4_LW, 0)) AS VA_GP_Q4_LWK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_LW, 0)) AS VA_PAX_JAN_APR_LWK,
SUM (NVL (VA_GP_JAN_APR_LW, 0)) AS VA_GP_JAN_APR_LWK,
----------------------
SUM (NVL (VA_PAX_Q1_PW, 0)) AS VA_PAX_Q1_PWK,
SUM (NVL (VA_GP_Q1_PW, 0)) AS VA_GP_Q1_PWK,
SUM (NVL (VA_PAX_Q2_PW, 0)) AS VA_PAX_Q2_PWK,
SUM (NVL (VA_GP_Q2_PW, 0)) AS VA_GP_Q2_PWK,
SUM (NVL (VA_PAX_Q3_PW, 0)) AS VA_PAX_Q3_PWK,
SUM (NVL (VA_GP_Q3_PW, 0)) AS VA_GP_Q3_PWK,
SUM (NVL (VA_PAX_Q4_PW, 0)) AS VA_PAX_Q4_PWK,
SUM (NVL (VA_GP_Q4_PW, 0)) AS VA_GP_Q4_PWK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_PW, 0)) AS VA_PAX_JAN_APR_PWK,
SUM (NVL (VA_GP_JAN_APR_PW, 0)) AS VA_GP_JAN_APR_PWK
FROM AW.jl_CUST_PROD_DTL_2017_YTD_rk AL1
LEFT JOIN AW.CM_CUST_PROD_TPC_CAT_DIM AL2 ON AL1.TPC_CAT_BRAND_KEY = AL2.TPC_CAT_KEY
LEFT JOIN AW.CM_CUST_PROD_TPC_CAT_DIM AL3 ON AL1.TPC_CAT_PROD_KEY = AL3.TPC_CAT_KEY
--WHERE cust_num = 16295
GROUP BY AL1.PRODUCT_YEAR,
AL1.BRAND,
REC,
FRQ,
MULTI_SEG,
TRV_STS,
EMAILABLE_FLG,
MAILABLE_FLG,
LAST_COMPANY_TRAVELLED,
GCCLR_EVER_NEVER,
GCCLS_EVER_NEVER,
OAT_EVER_NEVER,
GCT_EVER_NEVER,
ever_never,
RS_EVER,
SS_EVER,
AL2.TPC_CAT,
AL2.TPC_CAT_GRP_1,
AL2.TPC_CAT_GRP_5,
GCC_BK_FLG,
TPC_LEVEL_BRAND_DESC,
TPC_REC_CAT_BRAND_RANK,
TPC_REC_CAT_BRAND_KEY,
case when to_number( TPC_cust_brand_RANK_key) = 999 then 999 when to_number( TPC_cust_brand_RANK_key) >= 10 then 10 else to_number( TPC_cust_brand_RANK_key) end ,
PENDING_FLAG
-- TPC_REC_LEVEL_BRAND_DESC
--------------
-- Prod
------------
UNION ALL
SELECT PRODUCT_YEAR,
BRAND,
BRAND COMPANY,
PROD,
REC,
FRQ,
MULTI_SEG,
TRV_STS,
EMAILABLE_FLG,
MAILABLE_FLG,
LAST_COMPANY_TRAVELLED,
GCCLR_EVER_NEVER,
GCCLS_EVER_NEVER,
OAT_EVER_NEVER,
GCT_EVER_NEVER,
ever_never,
RS_EVER,
SS_EVER,
TPC_CAT TPC_CAT,
TPC_CAT_GRP_1,
TPC_CAT_GRP_5,
TPC_REC_CAT_PROD_RANK AS TPC_REC_RANK,
TPC_REC_CAT_PROD_KEY AS TPC_REC_CAT_KEY,
-- TPC_REC_LEVEL_PROD_DESC AS TPC_REC_LEVEL,
-- TPC_cust_prod_RANK as tpc_Rank,
-- TPC_cust_prod_RANK_key as tpc_rank_key,
case when to_number( TPC_cust_prod_RANK_key) = 999 then 999 when to_number( TPC_cust_prod_RANK_key) >= 10 then 10 else to_number( TPC_cust_prod_RANK_key) end as tpc_Rank,
0 TPC_NBR,
PROD_COUNT,
0 GCC_FY_RANK,
TPC_LEVEL,
PENDING_FLAG,
COUNT (DISTINCT CUST_NUM) UNV,
COUNT (DISTINCT CASE WHEN BK_FLG = 'Y' THEN CUST_NUM ELSE NULL END) HHR,
GCC_BK_FLG,
SUM (NVL (TRIP_CNT, 0)) TRIP_CNT,
SUM (NVL (PAX, 0)) PAX,
SUM (NVL (HHR_PROD, 0)) HHR_FIELD,
SUM (NVL (HHR_PROD_LW, 0)) HHR_FIELD_LW,
SUM (NVL (HHR_PROD_L4W, 0)) HHR_FIELD_L4W,
SUM (NVL (HHR_PROD_P4W, 0)) HHR_FIELD_P4W,
SUM (NVL (GP, 0)) GP,
SUM (NVL (ADV, 0)) ADV,
SUM (NVL (GP, 0)) - SUM (NVL (ADV, 0)) NP,
SUM (NVL (PAX_L4W, 0)) PAX_L4WK,
SUM (NVL (GP_L4W, 0)) GP_L4WK,
SUM (NVL (ADV_L4W, 0)) ADV_L4WK,
SUM (NVL (GP_L4W, 0)) - SUM (NVL (ADV_L4W, 0)) NP_L4WK,
SUM (NVL (PAX_P4W, 0)) PAX_P4WK,
SUM (NVL (GP_P4W, 0)) GP_P4WK,
SUM (NVL (ADV_P4W, 0)) ADV_P4WK,
SUM (NVL (GP_P4W, 0)) - SUM (NVL (ADV_P4W, 0)) NP_P4WK,
SUM (PAX_LW) PAX_LWK,
SUM (NVL (GP_LW, 0)) GP_LWK,
SUM (NVL (ADV_LW, 0)) ADV_LWK,
SUM (NVL (GP_LW, 0)) - SUM (NVL (ADV_LW, 0)) NP_LWK,
SUM (NVL (PAX_PW, 0)) PAX_PWK,
SUM (NVL (GP_PW, 0)) GP_PWK,
SUM (NVL (ADV_PW, 0)) ADV_PWK,
SUM (NVL (GP_PW, 0)) - SUM (NVL (ADV_PW, 0)) NP_PWK,
/*------------------------*/
SUM (NVL (PAX_Q1, 0)) AS PAX_Q1,
SUM (NVL (GP_Q1, 0)) AS GP_Q1,
SUM (NVL (PAX_Q2, 0)) AS PAX_Q2,
SUM (NVL (GP_Q2, 0)) AS GP_Q2,
SUM (NVL (PAX_Q3, 0)) AS PAX_Q3,
SUM (NVL (GP_Q3, 0)) AS GP_Q3,
SUM (NVL (PAX_Q4, 0)) AS PAX_Q4,
SUM (NVL (GP_Q4, 0)) AS GP_Q4,
---------------------
SUM (NVL (PAX_JAN_APR, 0)) AS PAX_JAN_APR,
SUM (NVL (GP_JAN_APR, 0)) AS GP_JAN_APR,
----------------------
SUM (NVL (PAX_Q1_L4W, 0)) AS PAX_Q1_L4WK,
SUM (NVL (GP_Q1_L4W, 0)) AS GP_Q1_L4WK,
SUM (NVL (PAX_Q2_L4W, 0)) AS PAX_Q2_L4WK,
SUM (NVL (GP_Q2_L4W, 0)) AS GP_Q2_L4WK,
SUM (NVL (PAX_Q3_L4W, 0)) AS PAX_Q3_L4WK,
SUM (NVL (GP_Q3_L4W, 0)) AS GP_Q3_L4WK,
SUM (NVL (PAX_Q4_L4W, 0)) AS PAX_Q4_L4WK,
SUM (NVL (GP_Q4_L4W, 0)) AS GP_Q4_L4WK,
---------------------
SUM (NVL (PAX_JAN_APR_L4W, 0)) AS PAX_JAN_APR_L4Wk,
SUM (NVL (GP_JAN_APR_L4W, 0)) AS GP_JAN_APR_L4Wk,
----------------------
SUM (NVL (PAX_Q1_P4W, 0)) AS PAX_Q1_P4WK,
SUM (NVL (GP_Q1_P4W, 0)) AS GP_Q1_P4WK,
SUM (NVL (PAX_Q2_P4W, 0)) AS PAX_Q2_P4WK,
SUM (NVL (GP_Q2_P4W, 0)) AS GP_Q2_P4WK,
SUM (NVL (PAX_Q3_P4W, 0)) AS PAX_Q3_P4WK,
SUM (NVL (GP_Q3_P4W, 0)) AS GP_Q3_P4WK,
SUM (NVL (PAX_Q4_P4W, 0)) AS PAX_Q4_P4WK,
SUM (NVL (GP_Q4_P4W, 0)) AS GP_Q4_P4WK,
---------------------
SUM (NVL (PAX_JAN_APR_P4W, 0)) AS PAX_JAN_APR_P4WK,
SUM (NVL (GP_JAN_APR_P4W, 0)) AS GP_JAN_APR_P4WK,
----------------------
SUM (NVL (PAX_Q1_LW, 0)) AS PAX_Q1_LWK,
SUM (NVL (GP_Q1_LW, 0)) AS GP_Q1_LWK,
SUM (NVL (PAX_Q2_LW, 0)) AS PAX_Q2_LWK,
SUM (NVL (GP_Q2_LW, 0)) AS GP_Q2_LWK,
SUM (NVL (PAX_Q3_LW, 0)) AS PAX_Q3_LWK,
SUM (NVL (GP_Q3_LW, 0)) AS GP_Q3_LWK,
SUM (NVL (PAX_Q4_LW, 0)) AS PAX_Q4_LWK,
SUM (NVL (GP_Q4_LW, 0)) AS GP_Q4_LWK,
---------------------
SUM (NVL (PAX_JAN_APR_LW, 0)) AS PAX_JAN_APR_WK,
SUM (NVL (GP_JAN_APR_LW, 0)) AS GP_JAN_APR_LWK,
----------------------
SUM (NVL (PAX_Q1_PW, 0)) AS PAX_Q1_PWK,
SUM (NVL (GP_Q1_PW, 0)) AS GP_Q1_PWK,
SUM (NVL (PAX_Q2_PW, 0)) AS PAX_Q2_PWK,
SUM (NVL (GP_Q2_PW, 0)) AS GP_Q2_PWK,
SUM (NVL (PAX_Q3_PW, 0)) AS PAX_Q3_PWK,
SUM (NVL (GP_Q3_PW, 0)) AS GP_Q3_PWK,
SUM (NVL (PAX_Q4_PW, 0)) AS PAX_Q4_PWK,
SUM (NVL (GP_Q4_PW, 0)) AS GP_Q4_PWK,
---------------------
SUM (NVL (PAX_JAN_APR_PW, 0)) AS PAX_JAN_APR_PWK,
SUM (NVL (GP_JAN_APR_PW, 0)) AS GP_JAN_APR_PWK,
----------------------
---------------------------------
SUM (NVL (VA_PAX, 0)) VA_PAX,
SUM (NVL (VA_GP, 0)) VA_GP,
SUM (NVL (VA_PAX_L4W, 0)) VA_PAX_L4WK,
SUM (NVL (VA_GP_L4W, 0)) VA_GP_L4WK,
SUM (NVL (VA_PAX_P4W, 0)) VA_PAX_P4WK,
SUM (NVL (VA_GP_P4W, 0)) VA_GP_P4WK,
SUM (VA_PAX_LW) VA_PAX_LWK,
SUM (NVL (VA_GP_LW, 0)) VA_GP_LWK,
SUM (NVL (VA_PAX_PW, 0)) VA_PAX_PWK,
SUM (NVL (VA_GP_PW, 0)) VA_GP_PWK,
/*------------------------*/
SUM (NVL (VA_PAX_Q1, 0)) AS VA_PAX_Q1,
SUM (NVL (VA_GP_Q1, 0)) AS VA_GP_Q1,
SUM (NVL (VA_PAX_Q2, 0)) AS VA_PAX_Q2,
SUM (NVL (VA_GP_Q2, 0)) AS VA_GP_Q2,
SUM (NVL (VA_PAX_Q3, 0)) AS VA_PAX_Q3,
SUM (NVL (VA_GP_Q3, 0)) AS VA_GP_Q3,
SUM (NVL (VA_PAX_Q4, 0)) AS VA_PAX_Q4,
SUM (NVL (VA_GP_Q4, 0)) AS VA_GP_Q4,
---------------------
SUM (NVL (VA_PAX_JAN_APR, 0)) AS VA_PAX_JAN_APR,
SUM (NVL (VA_GP_JAN_APR, 0)) AS VA_GP_JAN_APR,
----------------------
SUM (NVL (VA_PAX_Q1_L4W, 0)) AS VA_PAX_Q1_L4WK,
SUM (NVL (VA_GP_Q1_L4W, 0)) AS VA_GP_Q1_L4WK,
SUM (NVL (VA_PAX_Q2_L4W, 0)) AS VA_PAX_Q2_L4WK,
SUM (NVL (VA_GP_Q2_L4W, 0)) AS VA_GP_Q2_L4WK,
SUM (NVL (VA_PAX_Q3_L4W, 0)) AS VA_PAX_Q3_L4WK,
SUM (NVL (VA_GP_Q3_L4W, 0)) AS VA_GP_Q3_L4WK,
SUM (NVL (VA_PAX_Q4_L4W, 0)) AS VA_PAX_Q4_L4WK,
SUM (NVL (VA_GP_Q4_L4W, 0)) AS VA_GP_Q4_L4WK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_L4W, 0)) AS VA_PAX_JAN_APR_L4WK,
SUM (NVL (VA_GP_JAN_APR_L4W, 0)) AS VA_GP_JAN_APR_L4WK,
----------------------
SUM (NVL (VA_PAX_Q1_P4W, 0)) AS VA_PAX_Q1_P4WK,
SUM (NVL (VA_GP_Q1_P4W, 0)) AS VA_GP_Q1_P4WK,
SUM (NVL (VA_PAX_Q2_P4W, 0)) AS VA_PAX_Q2_P4WK,
SUM (NVL (VA_GP_Q2_P4W, 0)) AS VA_GP_Q2_P4WK,
SUM (NVL (VA_PAX_Q3_P4W, 0)) AS VA_PAX_Q3_P4WK,
SUM (NVL (VA_GP_Q3_P4W, 0)) AS VA_GP_Q3_P4WK,
SUM (NVL (VA_PAX_Q4_P4W, 0)) AS VA_PAX_Q4_P4WK,
SUM (NVL (VA_GP_Q4_P4W, 0)) AS VA_GP_Q4_P4WK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_P4W, 0)) AS VA_PAX_JAN_APR_P4WK,
SUM (NVL (VA_GP_JAN_APR_P4W, 0)) AS VA_GP_JAN_APR_P4WK,
----------------------
SUM (NVL (VA_PAX_Q1_LW, 0)) AS VA_PAX_Q1_LWK,
SUM (NVL (VA_GP_Q1_LW, 0)) AS VA_GP_Q1_LWK,
SUM (NVL (VA_PAX_Q2_LW, 0)) AS VA_PAX_Q2_LWK,
SUM (NVL (VA_GP_Q2_LW, 0)) AS VA_GP_Q2_LWK,
SUM (NVL (VA_PAX_Q3_LW, 0)) AS VA_PAX_Q3_LWK,
SUM (NVL (VA_GP_Q3_LW, 0)) AS VA_GP_Q3_LWK,
SUM (NVL (VA_PAX_Q4_LW, 0)) AS VA_PAX_Q4_LWK,
SUM (NVL (VA_GP_Q4_LW, 0)) AS VA_GP_Q4_LWK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_LW, 0)) AS VA_PAX_JAN_APR_LWK,
SUM (NVL (VA_GP_JAN_APR_LW, 0)) AS VA_GP_JAN_APR_LWK,
----------------------
SUM (NVL (VA_PAX_Q1_PW, 0)) AS VA_PAX_Q1_PWK,
SUM (NVL (VA_GP_Q1_PW, 0)) AS VA_GP_Q1_PWK,
SUM (NVL (VA_PAX_Q2_PW, 0)) AS VA_PAX_Q2_PWK,
SUM (NVL (VA_GP_Q2_PW, 0)) AS VA_GP_Q2_PWK,
SUM (NVL (VA_PAX_Q3_PW, 0)) AS VA_PAX_Q3_PWK,
SUM (NVL (VA_GP_Q3_PW, 0)) AS VA_GP_Q3_PWK,
SUM (NVL (VA_PAX_Q4_PW, 0)) AS VA_PAX_Q4_PWK,
SUM (NVL (VA_GP_Q4_PW, 0)) AS VA_GP_Q4_PWK,
---------------------
SUM (NVL (VA_PAX_JAN_APR_PW, 0)) AS VA_PAX_JAN_APR_PWK,
SUM (NVL (VA_GP_JAN_APR_PW, 0)) AS VA_GP_JAN_APR_PWK
FROM (SELECT PRODUCT_YEAR,
AL1.CUST_NUM,
AL1.BRAND,
AL1.PROD,
COMPANY,
REC,
FRQ,
MULTI_SEG,
TRV_STS,
EMAILABLE_FLG,
MAILABLE_FLG,
LAST_COMPANY_TRAVELLED,
GCCLR_EVER_NEVER,
GCCLS_EVER_NEVER,
OAT_EVER_NEVER,
GCT_EVER_NEVER,
ever_never,
RS_EVER,
SS_EVER,
AL2.TPC_CAT,
AL2.TPC_CAT_GRP_1,
AL2.TPC_CAT_GRP_5,
TPC_REC_CAT_PROD_RANK,
TPC_REC_CAT_PROD_KEY,
TPC_cust_prod_RANK ,
TPC_cust_prod_RANK_key ,
PENDING_FLAG,
-- TPC_REC_LEVEL_PROD_DESC,
CASE WHEN AL2.TPC_CAT_BUCKET >= 3 THEN SUM (1) OVER (PARTITION BY CUST_NUM, BRAND ORDER BY AL2.TPC_CAT_BUCKET DESC) ELSE 0 END AS PROD_COUNT,
BK_FLG,
TPC_LEVEL_PROD_DESC AS TPC_LEVEL,
GCC_BK_FLG,
TRIP_CNT,
PAX,
HHR_PROD,
HHR_PROD_LW,
HHR_PROD_L4W,
HHR_PROD_P4W,
GP,
PAX_L4W,
GP_L4W,
PAX_P4W,
GP_P4W,
PAX_LW,
GP_LW,
PAX_PW,
GP_PW,
PAX_Q1,
GP_Q1,
PAX_Q2,
GP_Q2,
PAX_Q3,
GP_Q3,
PAX_Q4,
GP_Q4,
-------------
PAX_JAN_APR,
GP_JAN_APR,
-----------
PAX_Q1_L4W,
GP_Q1_L4W,
PAX_Q2_L4W,
GP_Q2_L4W,
PAX_Q3_L4W,
GP_Q3_L4W,
PAX_Q4_L4W,
GP_Q4_L4W,
-------------
PAX_JAN_APR_L4W,
GP_JAN_APR_L4W,
-----------
PAX_Q1_P4W,
GP_Q1_P4W,
PAX_Q2_P4W,
GP_Q2_P4W,
PAX_Q3_P4W,
GP_Q3_P4W,