lzykevin
4/28/2020 - 9:19 AM
查询代理人销量收入 #zhusanjiao
查询代理人销量收入 #zhusanjiao
--查询代理人销量收入
SELECT CASE WHEN t.flight_date BETWEEN '20200401' AND '20200422' THEN '本期' ELSE '环期' END 周期,
q.名称,
CASE WHEN ((q.渠道划分 = 'B2C' AND t.tour LIKE '%XC%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%TC%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%TB%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%QN%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%HB%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%JD%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%MT%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%TN%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%ST%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%HT%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%517%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%LMM%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%BQ%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%KX%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%DP%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%FCZ%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%PA%')) THEN 'B2C旗舰店'
WHEN (q.渠道划分 = 'B2C' AND (t.tour NOT LIKE '%XC%' AND t.tour NOT LIKE '%TC%' AND t.tour NOT LIKE '%TB%' AND
t.tour NOT LIKE '%QN%' AND t.tour NOT LIKE '%HB%' AND t.tour NOT LIKE '%JD%' AND
t.tour NOT LIKE '%MT%' AND t.tour NOT LIKE '%TN%' AND t.tour NOT LIKE '%ST%' AND
t.tour NOT LIKE '%HT%' AND t.tour NOT LIKE '%517%' AND t.tour NOT LIKE '%LMM%' AND
t.tour NOT LIKE '%BQ%' AND t.tour NOT LIKE '%KX%' AND t.tour NOT LIKE '%DP%' AND
t.tour NOT LIKE '%FCZ%' AND t.tour NOT LIKE '%PA%' OR t.tour IS NULL)) THEN 'B2C'
WHEN q.渠道划分 = 'B2B' THEN '区域供应商'
WHEN q.渠道划分 IS NULL THEN '传统分销商'
WHEN q.渠道划分 IN ('外航', '旅行社', '境外') THEN '传统分销商'
ELSE q.渠道划分 END 渠道,
count(*) 销量,
sum(t.ft_fnum) 收入
FROM yxadmin.zh_tcn t LEFT JOIN dcdb.dailiren q ON (NVL((nvl(hy_agent_code, agent_code)), agtn) = q.代理人号)
WHERE (t.flight_date BETWEEN '20200401' AND '20200422' OR t.flight_date BETWEEN '20200301' AND '20200322')
-- AND t.dais BETWEEN '20200101' AND '20200131'
AND q.营业部 = '深圳' --限制深圳营业部
-- AND t.orac ='SZX' --限制深圳始发
AND (t.enrs NOT LIKE 'EXC%' OR t.enrs IS NULL) --剔除改期换开后客票
AND t.fptp1 != 'EX' --剔除改期换开后客票
AND t.carr = 'ZH' --承运人限制深航
AND t.ftnr <> 'OPEN' --剔除sdafd1只有年的数据
AND orac NOT IN
('BKK', 'PEN', 'CJU', 'CGK', 'HAN', 'HKT', 'ICN', 'KIX', 'KUL', 'NRT', 'TPE', 'LHR', 'MNL', 'PNH', 'SIN', 'HKG',
'MFM', 'CAI', 'HND') --剔除始发为国际
AND dstc NOT IN
('BKK', 'PEN', 'CJU', 'CGK', 'HAN', 'HKT', 'ICN', 'KIX', 'KUL', 'NRT', 'TPE', 'LHR', 'MNL', 'PNH', 'SIN', 'HKG',
'MFM', 'CAI', 'HND') --剔除到达为国际
GROUP BY CASE WHEN t.flight_date BETWEEN '20200401' AND '20200422' THEN '本期' ELSE '环期' END,
q.名称,
CASE WHEN ((q.渠道划分 = 'B2C' AND t.tour LIKE '%XC%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%TC%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%TB%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%QN%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%HB%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%JD%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%MT%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%TN%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%ST%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%HT%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%517%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%LMM%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%BQ%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%KX%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%DP%') OR (q.渠道划分 = 'B2C' AND t.tour LIKE '%FCZ%') OR
(q.渠道划分 = 'B2C' AND t.tour LIKE '%PA%')) THEN 'B2C旗舰店'
WHEN (q.渠道划分 = 'B2C' AND (t.tour NOT LIKE '%XC%' AND t.tour NOT LIKE '%TC%' AND t.tour NOT LIKE '%TB%' AND
t.tour NOT LIKE '%QN%' AND t.tour NOT LIKE '%HB%' AND t.tour NOT LIKE '%JD%' AND
t.tour NOT LIKE '%MT%' AND t.tour NOT LIKE '%TN%' AND t.tour NOT LIKE '%ST%' AND
t.tour NOT LIKE '%HT%' AND t.tour NOT LIKE '%517%' AND
t.tour NOT LIKE '%LMM%' AND t.tour NOT LIKE '%BQ%' AND
t.tour NOT LIKE '%KX%' AND t.tour NOT LIKE '%DP%' AND
t.tour NOT LIKE '%FCZ%' AND t.tour NOT LIKE '%PA%' OR t.tour IS NULL))
THEN 'B2C'
WHEN q.渠道划分 = 'B2B' THEN '区域供应商'
WHEN q.渠道划分 IS NULL THEN '传统分销商'
WHEN q.渠道划分 IN ('外航', '旅行社', '境外') THEN '传统分销商'
ELSE q.渠道划分 END