sql: select emp.employee_number "工号", per_national_id.national_id "身份证号", emp.name "姓名", emp_job.hire_date "入职日期", emp.termination_date "离职日期", employee_type_trans.value "用工类型", legal_entity.name "公司主体", sum(case when summary.date_integer=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "当月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "当月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "当月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "当月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "当月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "当月应缴个税", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-1 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "前一月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-1 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "前一月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-1 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "前一月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-1 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "前一月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-1 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "前一月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-1 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "前一月应缴个税", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-2 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "前二月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-2 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "前二月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-2 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "前二月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-2 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "前二月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-2 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "前二月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-2 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "前二月应缴个税", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-3 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "前三月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-3 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "前三月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-3 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "前三月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-3 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "前三月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-3 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "前三月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-3 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "前三月应缴个税", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-4 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "前四月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-4 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "前四月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-4 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "前四月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-4 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "前四月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-4 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "前四月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-4 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "前四月应缴个税", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-5 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "前五月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-5 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "前五月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-5 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "前五月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-5 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "前五月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-5 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "前五月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-5 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "前五月应缴个税", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-6 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "前六月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-6 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "前六月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-6 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "前六月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-6 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "前六月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-6 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "前六月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-6 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "前六月应缴个税", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-7 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "前七月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-7 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "前七月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-7 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "前七月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-7 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "前七月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-7 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "前七月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-7 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "前七月应缴个税", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-8 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "前八月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-8 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "前八月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-8 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "前八月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-8 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "前八月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-8 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "前八月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-8 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "前八月应缴个税", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-9 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "前九月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-9 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "前九月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-9 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "前九月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-9 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "前九月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-9 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "前九月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-9 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "前九月应缴个税", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-10 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "前十月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-10 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "前十月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-10 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "前十月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-10 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "前十月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-10 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "前十月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-10 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "前十月应缴个税", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "前十一月应发工资", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "前十一月代扣小计", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "前十一月可税前抵扣通讯补贴", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "前十一月专项附加扣除", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "前十一月减除费用", sum(case when summary.date_integer=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "前十一月应缴个税", sum(case when summary.date_integer>=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.date_integer<=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code='Should_send' then summary.ptd else 0 end) "合计月应发工资", sum(case when summary.date_integer>=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.date_integer<=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code in ('Gerenshebao','Gerengjj') then summary.ptd else 0 end) "合计代扣小计", sum(case when summary.date_integer>=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.date_integer<=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Deduct_communication_subsidies' then summary.ptd else 0 end) "合计可税前抵扣通讯补贴", sum(case when summary.date_integer>=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.date_integer<=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'SPECIAL_ADDITIONAL_DEDUCTION' then summary.ptd else 0 end) "合计专项附加扣除", sum(case when summary.date_integer>=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.date_integer<=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'TAX_EXEMPTION' then summary.ptd else 0 end) "合计减除费用", sum(case when summary.date_integer>=(select to_number(to_char(current_date::timestamp + '-11 month','YYYYmm')::text, '9999999999999')) and summary.date_integer<=(select to_number(to_char(current_date,'YYYYmm')::text, '9999999999999')) and summary.balance_item_code = 'Taxes_payable' then summary.ptd else 0 end) "合计应缴个税" FROM emp_job LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.id LEFT JOIN per_person AS person ON emp.person_id = person.id left join per_national_id on per_national_id.person_id=person.id and per_national_id.is_primary=True left join employee_type on emp_job.employee_type=employee_type.id left join ir_translation employee_type_trans on employee_type_trans.res_id=employee_type.id and employee_type_trans.name ='employee.type,name' and employee_type_trans.lang='zh_CN' left join legal_entity on emp_job.x_cbgsnew=legal_entity.id left join view_payroll_balance_summary_result summary on summary.summary_type=2 and summary.employee_id=emp.id WHERE emp_job.eroad_index_id IS NOT NULL AND emp_job.eroad_start_date <= current_date AND (emp_job.eroad_end_date >= current_date OR emp_job.eroad_end_date IS NULL) AND emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL group by emp.employee_number,per_national_id.national_id,emp.name,emp_job.hire_date,emp.termination_date,employee_type_trans.value,legal_entity.name