佛山出SQL例子
`-- 呼出 老员工 `
`-- 1、信息表 （取出状态表和数据表关联 状态为1的，成功数据）`
drop table if EXISTS temp_info;
CREATE table temp_info
SELECT
a.operating_center,
a.date,
a.name,
a.workdays_1,
a.workdays_2,
a.workdays_3,
a.job_number,
a.id_card_no,
a.wage_card,
a.bank_info,
a.out_company_info,
`-- 这一步是因为系统有些空值，开发人员默认为了其他值，使用case when then 判断`
(CASE
when a.out_company_fee!=-999999 then a.out_company_fee
else
0
END) out_company_fee,
a.employee_attribute,
a.mount_guard_date,
a.departure_date,
a.departure_flag,
a.phone_no,
(CASE
when a.seniority_pay!=-999999 then a.seniority_pay
else
0
END) seniority_pay,
(CASE
when a.award_punish!=-999999 then a.award_punish
else
0
END) award_punish,
(CASE
when a.cash_award_punish!=-999999 then a.cash_award_punish
else
0
END) cash_award_punish,
(CASE
when a.attendance_bouns != -999999 then a.attendance_bouns
else
0
END) attendance_bouns,
a.deduction,
a.base
from
`-- 导入的数据表`
callout_employee_info a,
`-- 状态表`
callout_employee_info_importbat b
where a.batid=b.id
and b.status=1
`-- 这个是取时间，2019.02.01要计算2019.01.01-2019.01.31的数据，如果每次改时间会很麻烦，
`-- b.DataDate like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 ``-- MONTH),1,7),"%")) 得到的时间是2019-01% SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7)`
`-- select CURDATE() `
`-- select DATE_SUB(CURDATE(),INTERVAL 1 MONTH)`
`-- INTERVAL 时间间隔，那个填1 以这个月为标准取上个月`
and b.DataDate like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%"))
and a.operating_center='佛山运营中心'
and a.employee_attribute='老员工';
`-- 现金奖罚 信息表的现金奖罚来之新的一张表`
drop table if EXISTS temp_award_punish;
CREATE table temp_award_punish
select
a.operating_center,
a.wdate,
a.wname,
a.id_card_no,
SUM(
(CASE
when a.fee!=-999999 then a.fee
else
0
END)
) fee
from
callout_kfjl_info a,
callout_kfjl_info_importlog b
where a.batid=b.id
and b.dstate=1
and b.DataMonth like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%"))
and operating_center='佛山运营中心'
GROUP BY a.id_card_no;
`-- 3、社保 游琦姐会发这个月的社保，自己导入`
drop table if EXISTS temp_social;
CREATE table temp_social
select
ss_address,
ss_name,
ss_id_card_no,
ss_month,
ss_total_personal_benefits
from
social
where ss_month like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%"));
`-- 4、`内推奖励` 游琦姐会发这个月的社保，自己导入`
drop table if EXISTS temp_interpolate;
CREATE table temp_interpolate
select
i_idcard_of_referrer,
SUM(i_write_off_expenses_1+i_write_off_expenses_2+i_write_off_expenses_3+i_write_off_expenses_4+i_write_off_expenses_5) interpolate_total
from
interpolate
WHERE i_data_month like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%"))
GROUP BY i_idcard_of_referrer;
`-- 匹配奖励及扣罚 社保`
drop table if EXISTS temp_info_fee;
CREATE table temp_info_fee
SELECT
a.operating_center,
a.date,
a.name,
a.workdays_1,
a.workdays_2,
a.workdays_3,
a.job_number,
a.id_card_no,
a.wage_card,
a.bank_info,
a.out_company_info,
a.out_company_fee,
a.employee_attribute,
a.mount_guard_date,
a.departure_date,
a.departure_flag,
a.phone_no,
a.seniority_pay,
a.award_punish,
IFNULL(b.fee,0) cash_award_punish,
a.attendance_bouns,
a.deduction,
a.base,
IFNULL(c.ss_total_personal_benefits,0) ss_total_personal_benefits,
IFNULL(d.interpolate_total,0) interpolate_total
from temp_info a
LEFT JOIN temp_award_punish b ON a.id_card_no=b.id_card_no
left join temp_social c ON a.id_card_no=c.ss_id_card_no
left join temp_interpolate d ON a.id_card_no=d.i_idcard_of_referrer;
`-- d.i_idcard_of_the_referrer 内推 ss_total_personal_benefits 社保`
`-- 2、明细 工作量表`
drop table if EXISTS temp_details;
CREATE table temp_details
SELECT
a.operating_center,
a.operating_area,
a.date,
a.project_name,
a.deal_service,
a.deal_type,
a.reward_unit_price,
a.reward_percentage,
a.mobile_number,
a.job_number,
a.employee_name,
a.subscriber_number,
a.wage_level,
a.product_num_integral,
sum(a.product_num_integral) total
from
callout_staff_workload_month a,
callout_staff_workload_month_importbat b
where a.batid=b.id
and b.status=1
and b.DataDate like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%"))
and a.operating_center='佛山运营中心'
GROUP BY a.job_number;
`-- 3、信息表关联明细表`
DROP TABLE IF EXISTS temp_total;
`-- 存储表`
CREATE TABLE temp_total
select
a.operating_center,
a.date,
a.name,
a.workdays_1,
a.workdays_2,
a.workdays_3,
a.job_number,
a.id_card_no,
a.wage_card,
a.bank_info,
a.out_company_info,
a.out_company_fee,
a.employee_attribute,
a.mount_guard_date,
a.departure_date,
a.departure_flag,
a.phone_no,
a.seniority_pay,
a.award_punish,
a.cash_award_punish,
a.attendance_bouns,
a.deduction,
a.base,
a.ss_total_personal_benefits,
a.interpolate_total,
b.project_name,
b.deal_service,
b.deal_type,
b.reward_unit_price,
b.reward_percentage,
b.mobile_number,
b.subscriber_number,
b.wage_level,
b.product_num_integral,
b.total,
`-- 产值 产值+信息表的产值奖罚`
(CASE
when (IFNULL(b.total,0)+a.award_punish)>0
then (IFNULL(b.total,0)+a.award_punish)
else
0
END) product,
`-- 平均产值`
(CASE
when (IFNULL(b.total,0)+a.award_punish)>0
then
(CASE
when a.workdays_2>0
then (IFNULL(b.total,0)+a.award_punish) / a.workdays_2
else
0
END)
else
0
END) average
from temp_info_fee a LEFT JOIN temp_details b
ON a.job_number=b.job_number;
`-- 4、算出雅安的工资体系`
drop table if EXISTS temp_system;
CREATE table temp_system
SELECT
a.wage_level,
a.computation_rule,
a.production_num_min,
a.production_num_max,
a.cardinal_number,
a.draw_a_percentage,
a.base_pay,
a.quantity_award,
a.changes_of_subsidies,
a.keep_low_wage
from
callout_wage_system a,
callout_wage_system_importlog b
where a.batid=b.id
and b.dstate=1
and b.DataMonth like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),0,7),"%"))
and a.wage_level='佛山运营中心-老员工';
`-- 5、通过明细表的总产值和信息表产值奖罚，算出每个员工的月总提成`
DROP TABLE IF EXISTS temp_commission;
`-- 存储表`
CREATE TABLE temp_commission
select
c.operating_center,
c.date,
c.name,
c.workdays_1,
c.workdays_2,
c.workdays_3,
c.job_number,
c.id_card_no,
c.wage_card,
c.bank_info,
c.out_company_info,
c.out_company_fee,
c.employee_attribute,
c.mount_guard_date,
c.departure_date,
c.departure_flag,
c.phone_no,
c.seniority_pay,
c.award_punish,
c.cash_award_punish,
c.attendance_bouns,
c.deduction,
c.base,
c.project_name,
c.deal_service,
c.deal_type,
c.reward_unit_price,
c.reward_percentage,
c.mobile_number,
c.subscriber_number,
c.product_num_integral,
c.product,
c.total,
c.average,
d.wage_level,
d.computation_rule,
d.production_num_min,
d.production_num_max,
d.cardinal_number,
d.draw_a_percentage,
d.base_pay,
d.quantity_award,
d.changes_of_subsidies,
d.keep_low_wage,
`-- 提成=总产值*提成单价*0.01*提成系数*0.01
(CASE
when (c.product-d.cardinal_number)<0 then 0
when (c.product-d.cardinal_number)>0
then (c.product-d.cardinal_number)
else
0
END)*d.draw_a_percentage*0.01*c.deduction*0.01 commission_amount,
`-- 变动补贴`
d.changes_of_subsidies*c.workdays_2 real_change_bonus,
`-- 底薪（基础工资）`
(CASE
when c.workdays_1>0 then d.base_pay/c.workdays_1*(c.workdays_2+c.workdays_3)
else
0
END)*0.01*c.base end_base_pay,
c.seniority_pay*0 post,
c.seniority_pay*0 bank_province,
c.seniority_pay*0 bank_cuty,
c.seniority_pay*0+100 synthesize,
c.seniority_pay*0 connect_long,
c.seniority_pay*0 performance_score,
`-- 社保`
c.ss_total_personal_benefits social_security,
`-- 内推`
c.interpolate_total restraining,
c.seniority_pay*0 the_performance_benefits,
c.seniority_pay*0 commission_amount_huru,
c.seniority_pay*0 individual_income_tax,
c.seniority_pay*0 actual_salary,
c.seniority_pay*0 shanghai_mobile_quality_penalty,
c.seniority_pay*0 make_up_last_month,
c.seniority_pay*0 `make_up_last_month's_salary`,
c.seniority_pay*0 cost_division,
c.seniority_pay*0 part_time_functions,
c.seniority_pay*0 travel_allowance
from temp_total c,temp_system d
where c.product BETWEEN d.production_num_min and d.production_num_max;
-- DROP TABLE IF EXISTS temp_out_salary_1;
`-- 存储表`
-- CREATE TABLE temp_out_salary_1
`-- 6、工资`
drop table if exists temp_out_salary_1;
create table temp_out_salary_1
select
operating_center '运营中心',
post '岗位',
name '员工姓名',
job_number '员工工号',
id_card_no '身份证号',
wage_card '工资账号',
bank_province '开户行',
bank_cuty '开户地省',
bank_info '开户地市',
end_base_pay '基础工资',
seniority_pay '工龄工资',
the_performance_benefits '履约补助',
workdays_1 '应上班天数',
workdays_2 '实际上班天数',
workdays_3 '带薪天数',
connect_long '总接电量',
total '总产值',
award_punish '产量奖/罚',
average '平均产值',
performance_score '绩效得分',
CONCAT(draw_a_percentage,"%") '提成单价',
CONCAT(base,"%") '基础系数',
CONCAT(deduction,"%") '提成系数',
CONCAT(synthesize,"%") '综合系数',
commission_amount_huru '呼入提成',
commission_amount '呼出提成',
part_time_functions '绩效工资—职能、兼职管理人员绩效',
travel_allowance '出差补贴',
attendance_bouns '全勤',
cash_award_punish '现金奖/罚',
real_change_bonus '变动补贴',
quantity_award '达量奖',
social_security '个人社保扣款',
restraining '内推奖励',
shanghai_mobile_quality_penalty '上海移动质量扣罚',
`make_up_last_month's_salary` '补发上月工资',
cost_division '成本划分',
(CASE
`-- 判断第一步 信息表的 表头 否恶意离职 是合计工资为0，`
when departure_flag='是' then 0
`-- 判断第二步 信息表的 表头 否恶意离职 否 合计工资再判断，那些金额加起来<0,合计工资为0,反之给合计工资`
when
(departure_flag='否' or departure_flag='')
and ( departure_flag='否' or departure_flag='')
and (
quantity_award+seniority_pay+cash_award_punish+commission_amount+
real_change_bonus+end_base_pay+attendance_bouns+social_security+restraining
)<0
then 0
when
(departure_flag='否' or departure_flag='')
and ( departure_flag='否' or departure_flag='')
and (
quantity_award+seniority_pay+cash_award_punish+commission_amount+
real_change_bonus+end_base_pay+attendance_bouns+social_security+restraining
)>0
then
round(
quantity_award+seniority_pay+cash_award_punish+commission_amount+real_change_bonus
#NAME?
,2)
else
0
END) 税前工资合计',
individual_income_tax '个人所得税',
actual_salary '实发工资金额',
SUBSTR( mount_guard_date, 1, 10 ) '入职时间',
SUBSTR( departure_date, 1, 10 ) '离职时间',
departure_flag '是否恶意离职',
phone_no '联系电话'
from temp_commission;
`-- 呼出 M1员工 `
`-- 1、信息表`
drop table if EXISTS temp_info;
CREATE table temp_info
SELECT
a.operating_center,
a.date,
a.name,
a.workdays_1,
a.workdays_2,
a.workdays_3,
a.job_number,
a.id_card_no,
a.wage_card,
a.bank_info,
a.out_company_info,
(CASE
when a.out_company_fee!=-999999 then a.out_company_fee
else
0
END) out_company_fee,
a.employee_attribute,
a.mount_guard_date,
a.departure_date,
a.departure_flag,
a.phone_no,
(CASE
when a.seniority_pay!=-999999 then a.seniority_pay
else
0
END) seniority_pay,
(CASE
when a.award_punish!=-999999 then a.award_punish
else
0
END) award_punish,
(CASE
when a.cash_award_punish!=-999999 then a.cash_award_punish
else
0
END) cash_award_punish,
(CASE
when a.attendance_bouns != -999999 then a.attendance_bouns
else
0
END) attendance_bouns,
a.deduction,
a.base
from
callout_employee_info a,
callout_employee_info_importbat b
where a.batid=b.id
and b.status=1
and b.DataDate like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%"))
and a.operating_center='佛山运营中心'
and a.employee_attribute='M1员工';
`-- 现金奖罚`
drop table if EXISTS temp_award_punish;
CREATE table temp_award_punish
select
a.operating_center,
a.wdate,
a.wname,
a.id_card_no,
SUM(
(CASE
when a.fee!=-999999 then a.fee
else
0
END)
) fee
from
callout_kfjl_info a,callout_kfjl_info_importlog b
where a.batid=b.id
and b.dstate=1
and b.DataMonth like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%"))
and operating_center='佛山运营中心'
GROUP BY a.id_card_no;
`-- 3、社保`
drop table if EXISTS temp_social;
CREATE table temp_social
select
ss_address,
ss_name,
ss_id_card_no,
ss_month,
ss_total_personal_benefits
from
social
where ss_month like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%"));
`-- 4、`内推奖励``
drop table if EXISTS temp_interpolate;
CREATE table temp_interpolate
select
i_idcard_of_referrer,
SUM(i_write_off_expenses_1+i_write_off_expenses_2+i_write_off_expenses_3+i_write_off_expenses_4+i_write_off_expenses_5) interpolate_total
from
interpolate
WHERE i_data_month like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%"))
GROUP BY i_idcard_of_referrer;
`-- 匹配奖励及扣罚 社保`
drop table if EXISTS temp_info_fee;
CREATE table temp_info_fee
SELECT
a.operating_center,
a.date,
a.name,
a.workdays_1,
a.workdays_2,
a.workdays_3,
a.job_number,
a.id_card_no,
a.wage_card,
a.bank_info,
a.out_company_info,
a.out_company_fee,
a.employee_attribute,
a.mount_guard_date,
a.departure_date,
a.departure_flag,
a.phone_no,
a.seniority_pay,
a.award_punish,
IFNULL(b.fee,0) cash_award_punish,
a.attendance_bouns,
a.deduction,
a.base,
IFNULL(c.ss_total_personal_benefits,0) ss_total_personal_benefits,
IFNULL(d.interpolate_total,0) interpolate_total
from temp_info a
LEFT JOIN temp_award_punish b ON a.id_card_no=b.id_card_no
left join temp_social c ON a.id_card_no=c.ss_id_card_no
left join temp_interpolate d ON a.id_card_no=d.i_idcard_of_referrer;
-- d.i_idcard_of_the_referrer 内推 ss_total_personal_benefits 社保
`-- 2、明细`
drop table if EXISTS temp_details;
CREATE table temp_details
SELECT
a.operating_center,
a.operating_area,
a.date,
a.project_name,
a.deal_service,
a.deal_type,
a.reward_unit_price,
a.reward_percentage,
a.mobile_number,
a.job_number,
a.employee_name,
a.subscriber_number,
a.wage_level,
a.product_num_integral,
sum(a.product_num_integral) total
from
callout_staff_workload_month a,
callout_staff_workload_month_importbat b
where a.batid=b.id
and b.status=1
and b.DataDate like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),1,7),"%"))
and a.operating_center='佛山运营中心'
GROUP BY a.job_number;
`-- 3、信息表关联明细表`
DROP TABLE IF EXISTS temp_total;
`-- 存储表`
CREATE TABLE temp_total
select
a.operating_center,
a.date,
a.name,
a.workdays_1,
a.workdays_2,
a.workdays_3,
a.job_number,
a.id_card_no,
a.wage_card,
a.bank_info,
a.out_company_info,
a.out_company_fee,
a.employee_attribute,
a.mount_guard_date,
a.departure_date,
a.departure_flag,
a.phone_no,
a.seniority_pay,
a.award_punish,
a.cash_award_punish,
a.attendance_bouns,
a.deduction,
a.base,
a.ss_total_personal_benefits,
a.interpolate_total,
b.project_name,
b.deal_service,
b.deal_type,
b.reward_unit_price,
b.reward_percentage,
b.mobile_number,
b.subscriber_number,
b.wage_level,
b.product_num_integral,
b.total,
(CASE
when (IFNULL(b.total,0)+a.award_punish)>0
then (IFNULL(b.total,0)+a.award_punish)
else
0
END) product,
(CASE
when (IFNULL(b.total,0)+a.award_punish)>0
then
(CASE
when a.workdays_2>0
then (IFNULL(b.total,0)+a.award_punish) / a.workdays_2
else
0
END)
else
0
END) average
from temp_info_fee a LEFT JOIN temp_details b
ON a.job_number=b.job_number;
`-- 4、算出雅安的工资体系`
drop table if EXISTS temp_system;
CREATE table temp_system
SELECT
a.wage_level,
a.computation_rule,
a.production_num_min,
a.production_num_max,
a.cardinal_number,
a.draw_a_percentage,
a.base_pay,
a.quantity_award,
a.changes_of_subsidies,
a.keep_low_wage
from
callout_wage_system a,
callout_wage_system_importlog b
where a.batid=b.id
and b.dstate=1
and b.DataMonth like (select CONCAT(SUBSTR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),0,7),"%"))
and a.wage_level='佛山运营中心-M1员工';
`-- 5、通过明细表的总产值和信息表产值奖罚，算出每个员工的月总提成`
DROP TABLE IF EXISTS temp_commission;
`-- 存储表`
CREATE TABLE temp_commission
select
c.operating_center,
c.date,
c.name,
c.workdays_1,
c.workdays_2,
c.workdays_3,
c.job_number,
c.id_card_no,
c.wage_card,
c.bank_info,
c.out_company_info,
c.out_company_fee,
c.employee_attribute,
c.mount_guard_date,
c.departure_date,
c.departure_flag,
c.phone_no,
c.seniority_pay,
c.award_punish,
c.cash_award_punish,
c.attendance_bouns,
c.deduction,
c.base,
c.project_name,
c.deal_service,
c.deal_type,
c.reward_unit_price,
c.reward_percentage,
c.mobile_number,
c.subscriber_number,
c.product_num_integral,
c.product,
c.total,
c.average,
d.wage_level,
d.computation_rule,
d.production_num_min,
d.production_num_max,
d.cardinal_number,
d.draw_a_percentage,
d.base_pay,
d.quantity_award,
d.changes_of_subsidies,
d.keep_low_wage,
(CASE
when (c.product-d.cardinal_number)<0 then 0
when (c.product-d.cardinal_number)>0
then (c.product-d.cardinal_number)*d.draw_a_percentage*0.01
else
0
END)*c.deduction*0.01 commission_amount,
d.changes_of_subsidies*c.workdays_2 real_change_bonus,
(CASE
when c.workdays_1>0 then d.base_pay/c.workdays_1*(c.workdays_2+c.workdays_3)
else
0
END)*0.01*c.base end_base_pay,
c.seniority_pay*0 post,
c.seniority_pay*0 bank_province,
c.seniority_pay*0 bank_cuty,
c.seniority_pay*0+100 synthesize,
c.seniority_pay*0 connect_long,
c.seniority_pay*0 performance_score,
c.ss_total_personal_benefits social_security,
c.interpolate_total restraining,
c.seniority_pay*0 the_performance_benefits,
c.seniority_pay*0 commission_amount_huru,
c.seniority_pay*0 individual_income_tax,
c.seniority_pay*0 actual_salary,
c.seniority_pay*0 shanghai_mobile_quality_penalty,
c.seniority_pay*0 make_up_last_month,
c.seniority_pay*0 `make_up_last_month's_salary`,
c.seniority_pay*0 cost_division,
c.seniority_pay*0 part_time_functions,
c.seniority_pay*0 travel_allowance
from temp_total c,temp_system d
where c.product BETWEEN d.production_num_min and d.production_num_max;
-- DROP TABLE IF EXISTS temp_out_salary_1;
`-- 存储表`
-- CREATE TABLE temp_out_salary_1
`-- 6、工资`
drop table if exists temp_out_salary_2;
create table temp_out_salary_2
select
operating_center '运营中心',
post '岗位',
name '员工姓名',
job_number '员工工号',
id_card_no '身份证号',
wage_card '工资账号',
bank_province '开户行',
bank_cuty '开户地省',
bank_info '开户地市',
end_base_pay '基础工资',
seniority_pay '工龄工资',
the_performance_benefits '履约补助',
workdays_1 '应上班天数',
workdays_2 '实际上班天数',
workdays_3 '带薪天数',
connect_long '总接电量',
total '总产值',
award_punish '产量奖/罚',
average '平均产值',
performance_score '绩效得分',
CONCAT(draw_a_percentage,"%") '提成单价',
CONCAT(base,"%") '基础系数',
CONCAT(deduction,"%") '提成系数',
CONCAT(synthesize,"%") '综合系数',
commission_amount_huru '呼入提成',
commission_amount '呼出提成',
part_time_functions '绩效工资—职能、兼职管理人员绩效',
travel_allowance '出差补贴',
attendance_bouns '全勤',
cash_award_punish '现金奖/罚',
real_change_bonus '变动补贴',
quantity_award '达量奖',
social_security '个人社保扣款',
restraining '内推奖励',
shanghai_mobile_quality_penalty '上海移动质量扣罚',
`make_up_last_month's_salary` '补发上月工资',
cost_division '成本划分',
`-- 当M1员工有保底时，用合计工资判断保底 ； 保底=保底/（应上班天数*（实际上天数+带薪天数） keep_low_wage/(workdays_1*(workdays_2+workdays_3));;`
`-- 合计<保底 给 保底 ；合计>保底 给 ；合计`
(CASE
when departure_flag='是' then 0
when
(departure_flag='否' or departure_flag='')
and (
quantity_award+seniority_pay+cash_award_punish+commission_amount+
real_change_bonus+end_base_pay+attendance_bouns+social_security+restraining
)< keep_low_wage/(workdays_1*(workdays_2+workdays_3))
then keep_low_wage/(workdays_1*(workdays_2+workdays_3))
when (departure_flag='否' or departure_flag='') and (
quantity_award+seniority_pay+cash_award_punish+commission_amount+real_change_bonus+
end_base_pay+attendance_bouns+social_security+restraining
)> keep_low_wage/(workdays_1*(workdays_2+workdays_3))
then
`-- 呼入合计要乘以综合系数`
round(
quantity_award+seniority_pay+cash_award_punish+commission_amount+
real_change_bonus+end_base_pay+attendance_bouns+social_security+restraining
,2)
else
0
END) 税前工资合计',
individual_income_tax '个人所得税',
actual_salary '实发工资金额',
SUBSTR( mount_guard_date, 1, 10 ) '入职时间',
SUBSTR( departure_date, 1, 10 ) '离职时间',
departure_flag '是否恶意离职',
phone_no '联系电话'
from temp_commission;