SQLsummary/报表/期奖金拨比分析奖金金额汇总.sql

SET @PERIOD_MIN = 158;
SET @PERIOD_MAX = 158;

SELECT
A3.`期数`,
A2.`总金额`,
A2.`总PV`,
A3.`服务奖`,
A3.`服务奖原金额`,
A3.`推广奖`,
A3.`推广奖原金额`,
A3.`业绩奖`,
A3.`业绩奖原金额`,
A3.`消费奖`,
A3.`消费奖原金额`,
A3.`共享奖`,
A3.`共享奖原金额`,
A3.`管理奖`,
A3.`管理奖原金额`,
A3.`团队奖`,
A3.`团队奖原金额`,
A.`报单PV` * A3.`VIP奖参考比例` * 0.95 AS `VIP奖`,
A.`报单PV` * A3.`VIP奖参考比例` AS `VIP奖原金额`,
A2.`总PV` * A3.`荣衔奖参考比例` * 0.95 AS `荣衔奖`,
A2.`总PV` * A3.`荣衔奖参考比例` AS `荣衔奖原金额`,
SUM(A3.`服务奖`+
A3.`推广奖`+
A3.`业绩奖`+
A3.`消费奖`+
A3.`共享奖`+
A3.`管理奖`+
A3.`团队奖`+
A.`报单PV` * A3.`VIP奖参考比例`+
A2.`总PV` * A3.`荣衔奖参考比例`) AS 实际奖金,
SUM(A3.`服务奖原金额`+
A3.`推广奖原金额`+
A3.`业绩奖原金额`+
A3.`消费奖原金额`+
A3.`共享奖原金额`+
A3.`管理奖原金额`+
A3.`团队奖原金额`+
A.`报单PV` * A3.`VIP奖参考比例`+
A2.`总PV` * A3.`荣衔奖参考比例`) AS 总奖金,
SUM(A3.`服务奖原金额`+
A3.`推广奖原金额`+
A3.`业绩奖原金额`+
A3.`消费奖原金额`+
A3.`共享奖原金额`+
A3.`管理奖原金额`+
A3.`团队奖原金额`+
A.`报单PV` * A3.`VIP奖参考比例`+
A2.`总PV` * A3.`荣衔奖参考比例`)/A2.`总金额` AS 总金额占比,
SUM(A3.`服务奖原金额`+
A3.`推广奖原金额`+
A3.`业绩奖原金额`+
A3.`消费奖原金额`+
A3.`共享奖原金额`+
A3.`管理奖原金额`+
A3.`团队奖原金额`+
A.`报单PV` * A3.`VIP奖参考比例`+
A2.`总PV` * A3.`荣衔奖参考比例`)/A2.`总PV` AS 总PV占比
FROM
(SELECT
		SUM( O.`PV合计` ) AS 报单PV 
	FROM
		zr_order_vw O 
	WHERE
		O.`期数` >= @PERIOD_MIN AND O.`期数` <= @PERIOD_MAX
		AND O.`订单类型` = '报单') A, 
	 
 (SELECT
	SUM( O2.`商品金额` ) AS 总金额,
	SUM( O2.`PV合计` ) AS 总PV
	FROM 
		zr_order_vw O2 
	WHERE	O2.`期数` >= @PERIOD_MIN AND O2.`期数` <= @PERIOD_MAX ) A2,
(SELECT
	B.PERIOD_NUM AS 期数,
	SUM(B.BONUS_BD) AS 服务奖,
	SUM(B.ORI_BONUS_BD) AS 服务奖原金额,
	SUM(B.BONUS_TG) AS 推广奖,
	SUM(B.ORI_BONUS_TG) AS 推广奖原金额,
	SUM(B.BONUS_YJ) AS 业绩奖,
	SUM(B.ORI_BONUS_YJ) AS 业绩奖原金额,
	SUM(B.BONUS_XF) AS 消费奖,
	SUM(B.ORI_BONUS_XF) AS 消费奖原金额,
	SUM(B.BONUS_GX) AS 共享奖,
	SUM(B.ORI_BONUS_GX) AS 共享奖原金额,
	SUM(B.BONUS_GL) AS 管理奖,
	SUM(B.ORI_BONUS_GL) AS 管理奖原金额,
	SUM(B.BONUS_QY) AS 团队奖,
	SUM(B.ORI_BONUS_QY) AS 团队奖原金额,

	C.key_value AS VIP奖参考比例,
	C2.key_value AS 荣衔奖参考比例
FROM
	ar_calc_bonus B
	LEFT JOIN 参数表 C ON C.key_name = 'VIP奖参考比例'
	LEFT JOIN 参数表 C2 ON C2.key_name = '荣衔奖参考比例'
	WHERE B.PERIOD_NUM >= @PERIOD_MIN AND B.PERIOD_NUM <= @PERIOD_MAX) A3