91 lines
3.8 KiB
SQL
91 lines
3.8 KiB
SQL
select
|
|
stat_date -- 统计时间
|
|
,year(stat_date) AS stat_year
|
|
,month(stat_date) AS stat_month
|
|
,bu_code -- 事业部编码
|
|
,bu_name -- 事业部名称
|
|
,region_code -- 大区编码
|
|
,region_name -- 大区名称
|
|
,office_code -- 办事处编码
|
|
,office_name -- 办事处名称
|
|
,deal_code -- 经销商编码
|
|
,deal_name -- 经销商名称
|
|
,pl_name -- 产品线
|
|
,pi_name -- 品项
|
|
-- ,num -- 数量
|
|
-- ,amt -- 金额
|
|
,SUM(CASE WHEN indi_type = '消费者红包' THEN amt ELSE 0 END) AS consumer_amt -- 消费者红包金额
|
|
,SUM(CASE WHEN indi_type = '消费者红包' THEN num ELSE 0 END) AS consumer_num -- 消费者红包数量
|
|
|
|
,SUM(CASE WHEN indi_type = '出货数' THEN amt ELSE 0 END) AS ship_amt -- 出货金额
|
|
,SUM(CASE WHEN indi_type = '出货数' THEN num ELSE 0 END) AS ship_num -- 出货数量
|
|
|
|
,SUM(CASE WHEN indi_type = '卡券分润' THEN amt ELSE 0 END) AS coupons_amt -- 卡券分润金额
|
|
,SUM(CASE WHEN indi_type = '卡券分润' THEN num ELSE 0 END) AS coupons_num -- 卡券分润数量
|
|
|
|
,SUM(CASE WHEN indi_type = '酒券分润' THEN amt ELSE 0 END) AS wine_amt -- 酒券分润金额
|
|
,SUM(CASE WHEN indi_type = '酒券分润' THEN num ELSE 0 END) AS wine_num -- 酒券分润数量
|
|
|
|
,SUM(CASE WHEN indi_type = '消费者兑奖数' THEN amt ELSE 0 END) AS consumer_prize_amt -- 消费者兑奖金额
|
|
,SUM(CASE WHEN indi_type = '消费者兑奖数' THEN num ELSE 0 END) AS consumer_prize_num -- 消费者兑奖数量
|
|
|
|
,SUM(CASE WHEN indi_type = '扫码数' THEN amt ELSE 0 END) AS scan_amt -- 扫码数
|
|
,SUM(CASE WHEN indi_type = '扫码数' THEN num ELSE 0 END) AS scan_num -- 扫码数
|
|
|
|
,SUM(CASE WHEN indi_type = '消费者卡券' THEN amt ELSE 0 END) AS consumer_coup_amt -- 消费者卡券
|
|
,SUM(CASE WHEN indi_type = '消费者卡券' THEN num ELSE 0 END) AS consumer_coup_num -- 消费者卡券
|
|
|
|
,SUM(CASE WHEN indi_type = '终端销售奖' THEN amt ELSE 0 END) AS terminal_amt -- 终端销售奖
|
|
,SUM(CASE WHEN indi_type = '终端销售奖' THEN num ELSE 0 END) AS terminal_num -- 终端销售奖
|
|
|
|
,SUM(CASE WHEN indi_type = '异地扫码数' THEN amt ELSE 0 END) AS remote_amt -- 异地扫码数
|
|
,SUM(CASE WHEN indi_type = '异地扫码数' THEN num ELSE 0 END) AS remote_num -- 异地扫码数
|
|
|
|
,SUM(CASE WHEN indi_type = '现金分润' THEN amt ELSE 0 END) AS cash_amt -- 现金分润
|
|
,SUM(CASE WHEN indi_type = '现金分润' THEN num ELSE 0 END) AS cash_num -- 现金分润
|
|
from dm.v_mak_indicator_day a
|
|
-- 订单时间
|
|
WHERE stat_date >= date('${sStart_date}')
|
|
AND stat_date <= date('${sEnd_date}') + INTERVAL '1 days'
|
|
-- 产品线
|
|
${if(len(sPl_name) == 0,"","and a.pl_name in ('" + sPl_name + "')")}
|
|
-- 事业部
|
|
${if(len(sBu) == 0,"","and a.bu_code IN ('"+sBu+"')")}
|
|
-- 大区
|
|
${if(len(sRegion) == 0,"","and a.region_code IN ('"+ sRegion+"')")}
|
|
-- 办事处
|
|
${if(len(sOffice) == 0,"","and a.office_code IN ('"+ sOffice+"')")}
|
|
-- 经销商
|
|
${if(sSub_main == '主经销商',if(len(sDeal) == 0,"","and a.deal_code IN ('"+ sDeal+"')"),if(len(sDeal) == 0,"","and a.src_deal_code IN ('"+ sDeal +"')"))}
|
|
|
|
-- 权限控制
|
|
AND exists (
|
|
select
|
|
1
|
|
from
|
|
dm.dm_bi_user_permisson
|
|
where
|
|
bi_user = '${fine_username}'
|
|
and per_model IN ('all','sal')
|
|
and (
|
|
is_all = 1
|
|
OR per_code = a.bu_code -- 事业部
|
|
OR per_code = a.region_code -- 大区
|
|
OR per_code = a.office_code -- 办事处
|
|
|
|
)
|
|
)
|
|
group by
|
|
stat_date -- 统计时间
|
|
,bu_code -- 事业部编码
|
|
,bu_name -- 事业部名称
|
|
,region_code -- 大区编码
|
|
,region_name -- 大区名称
|
|
,office_code -- 办事处编码
|
|
,office_name -- 办事处名称
|
|
,deal_code -- 经销商编码
|
|
,deal_name -- 经销商名称
|
|
,pl_name -- 产品线
|
|
,pi_name -- 品项
|
|
,year(stat_date)
|
|
,month(stat_date) |