select '2013' per,
bk.prod,
seg.trav_on,
seg.rec,
seg.frq,
sum (bk.hh_prod) hhr,
sum (bk.net_pax) net_pax,
sum (bk.net_sales) net_sales,
sum (bk.gp) gp,
sum (0) va_gp,
sum (0) adv,
sysdate
from
(
select al3.base_code prod,
al1.ecust_number,
sum (al2.net_corp_hh_renewal) hh_corp,
sum (al2.net_base_code_hh_renewal) hh_prod,
sum (al2.net_pax) net_pax,
sum (al2.net_sale_amt) net_sales,
sum (al2.gross_profit) gp
from dm.dm_customer_dim al1,
dm.dm_dt_fct al2,
dm.dm_phm_vw al3,
dm.dm_date_dim al4
where al1.dm_customer_dkey = al2.dm_customer_dkey
and al3.dm_product_hkey = al2.dm_product_hkey
and al4.dm_date_dkey = al2.dm_transaction_dt_dkey
and al2.product_year = 2013
and al4.calendar_date <= trunc (sysdate, 'day') - 1
and al3.base_code in ('ACM')
group by al3.base_code,
al1.ecust_number
)
bk
left join
(
select trav_on,
rec,
frequency_grp_2 frq,
ecust_number
from aw.wrk_evr_nvr_cust_nbr_2013
)
seg
on bk.ecust_number = seg.ecust_number
group by bk.prod,
seg.trav_on,
seg.rec,
seg.frq
union all
select '2013' per,
va.prod,
seg.trav_on,
seg.rec,
seg.frq,
sum (0) hhr,
sum (0) net_pax,
sum (0) net_sales,
sum (0) gp,
sum (va.va_gp) va_gp,
sum (0) adv,
sysdate
from
(
select al3.base_code prod,
ref_cst.ecust_number,
sum (al2.gross_profit) va_gp
from dm.dm_dt_fct al2,
dm.dm_phm_vw al3,
dm.dm_date_dim al4,
aw.wrk_evr_nvr_cust_nbr_2013 al6,
dm.dm_customer_dim_epsilon_flag al7,
(
select al1.reservation_number,
al2.ecust_number
from dm.dm_dr_fct al1,
dm.dm_customer_dim al2,
dm.dm_date_dim al3
where al1.dm_referral_customer_dkey = al2.dm_customer_dkey
and al1.dm_reservation_dt_dkey = al3.dm_date_dkey
and al1.product_year = 2013
and al3.calendar_date <= trunc (sysdate, 'day') - 1
group by al1.reservation_number,
al2.ecust_number
)
ref_cst
where al2.reservation_number = ref_cst.reservation_number
and al3.dm_product_hkey = al2.dm_product_hkey
and al6.ecust_number = al7.ecust_number
and al7.dm_customer_dkey = al2.dm_customer_dkey
and al4.dm_date_dkey = al2.dm_transaction_dt_dkey
and al6.rec in ('VA')
and al2.product_year = 2013
and al4.calendar_date <= trunc (sysdate, 'day') - 1
and al3.base_code in ('ACM')
group by al3.base_code,
ref_cst.ecust_number
)
va
left join
(
select trav_on,
rec,
frequency_grp_2 frq,
ecust_number
from aw.wrk_evr_nvr_cust_nbr_2013
)
seg
on va.ecust_number = seg.ecust_number
group by va.prod,
seg.trav_on,
seg.rec,
seg.frq
union all
select '2013' per,
case
when al3.bc_summary = 'TMHTMR' then 'TMH'
when al3.bc_summary = 'YA5YA4' then 'YA4'
else al3.bc_summary
end prod,
al1.trav_on,
al1.rec,
al1.frequency_grp_2 frq,
0 hhr,
0 net_pax,
0 net_sales,
0 gp,
0 va_gp,
sum (al3.timed_unit_adv_cost) adv,
sysdate
from aw.wrk_evr_nvr_cust_nbr_2013 al1,
aw.cm_adv_2013 al3
where al1.ecust_number = al3.ecust_number
and al3.bc_summary in ('ACM')
group by al3.bc_summary,
al1.trav_on,
al1.rec,
al1.frequency_grp_2
SELECT '2013' PER,
AL3.BASE_CODE PROD,
SEG.TRAV_ON,
SEG.REC,
SEG.FREQUENCY_GRP_2 FRQ,
AL1.EPSILON_CUSTOMER_NUMBER,
SUM (AL2.NET_CORP_HH_RENEWAL) HH_CORP,
SUM (AL2.NET_BASE_CODE_HH_RENEWAL) HH_PROD,
SUM (AL2.NET_PAX) NET_PAX,
SUM (AL2.NET_SALE_AMT) NET_SALES,
SUM (AL2.GROSS_PROFIT) GP,
0 VA_GP,
0 ADV,
SYSDATE
FROM DM.DM_CUSTOMER_DIM AL1
JOIN DM.DM_DAILY_TRANSACTION_FCT AL2
ON AL1.DM_CUSTOMER_DKEY = AL2.DM_CUSTOMER_DKEY
JOIN AW.WRK_EVR_NVR_CUST_NBR_2013 SEG
ON AL1.EPSILON_CUSTOMER_NUMBER = SEG.EPSILON_CUSTOMER_NUMBER
JOIN DM.DM_PRODUCT_HIERARCHY_MKTRPT_VW AL3
ON AL3.DM_PRODUCT_HKEY = AL2.DM_PRODUCT_HKEY
JOIN DM.DM_DATE_DIM AL4
ON AL4.DM_DATE_DKEY = AL2.DM_TRANSACTION_DT_DKEY
WHERE AL2.PRODUCT_YEAR = 2013
AND AL4.CALENDAR_DATE <= TRUNC (SYSDATE, 'day') - 1
AND AL3.BASE_CODE IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR', 'ELB'
, 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA4', 'NEL', 'TMH', 'FCT',
'MYR')
GROUP BY
AL3.BASE_CODE PROD,
SEG.TRAV_ON,
SEG.REC,
SEG.FREQUENCY_GRP_2,
AL1.EPSILON_CUSTOMER_NUMBER
SELECT '2013' PER,
CASE
WHEN AL3.BASE_CODE_SUMMARY = 'TMHTMR' THEN 'TMH'
WHEN AL3.BASE_CODE_SUMMARY = 'YA5YA4' THEN 'YA4'
ELSE AL3.BASE_CODE_SUMMARY
END PROD,
AL1.TRAV_ON,
AL1.REC,
AL1.FREQUENCY_GRP_2 FRQ,
0 HHR,
0 NET_PAX,
0 NET_SALES,
0 GP,
0 VA_GP,
SUM (AL3.TIMED_UNIT_ADV_COST) ADV,
SYSDATE
FROM AW.WRK_EVR_NVR_CUST_NBR_2013 AL1,
AW.CM_ADV_2013 AL3
WHERE AL1.EPSILON_CUSTOMER_NUMBER = AL3.EPSILON_CUSTOMER_NUMBER
AND AL3.BASE_CODE_SUMMARY IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR',
'ELB', 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA5YA4', 'NEL', 'TMHTMR',
'FCT', 'MYR')
GROUP BY
CASE
WHEN AL3.BASE_CODE_SUMMARY = 'TMHTMR' THEN 'TMH'
WHEN AL3.BASE_CODE_SUMMARY = 'YA5YA4' THEN 'YA4'
ELSE AL3.BASE_CODE_SUMMARY
END,
AL1.TRAV_ON,
AL1.REC,
AL1.FREQUENCY_GRP_2
Plan
SELECT STATEMENT ALL_ROWSCost: 797,127 Bytes: 314,412 Cardinality: 5,516
4 HASH GROUP BY Cost: 797,127 Bytes: 314,412 Cardinality: 5,516
3 HASH JOIN Cost: 721,747 Bytes: 11,840,854,530 Cardinality: 207,734,290
1 TABLE ACCESS FULL TABLE AW.WRK_EVR_NVR_CUST_NBR_2012 Cost: 10,498 Bytes: 252,230,650 Cardinality: 7,206,590
2 TABLE ACCESS FULL TABLE AW.CM_ADV_2012_PIT Cost: 347,990 Bytes: 4,570,154,380 Cardinality: 207,734,290
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
12 Experts available now in Live!