select ssa.employee_id as x_employee_id, max(he.name) as x_employee_name,max(he.employee_number) as x_employee_number ,sum(case when pac.code='contract_bs' then ssal.after_amount else 0 end) as x_contract_bs ,max(case when pac.code='contract_bs' then ssal.validate_date else null end) as x_contract_bs_date ,sum(case when pac.code='position_allw' then ssal.after_amount else 0 end) as x_position_allw ,max(case when pac.code='position_allw' then ssal.validate_date else null end) as x_position_allw_date ,sum(case when pac.code='dinner_allw' then ssal.after_amount else 0 end) as x_dinner_allw ,max(case when pac.code='dinner_allw' then ssal.validate_date else null end) as x_dinner_allw_date ,sum(case when pac.code='transportation_allw' then ssal.after_amount else 0 end) as x_transportation_allw ,max(case when pac.code='transportation_allw' then ssal.validate_date else null end) as x_transportation_allw_date ,sum(case when pac.code='talk_fee_allw' then ssal.after_amount else 0 end) as x_talk_fee_allw ,max(case when pac.code='talk_fee_allw' then ssal.validate_date else null end) as x_talk_fee_allw_date ,sum(case when pac.code='housing_subsidy' then ssal.after_amount else 0 end) as x_housing_subsidy ,max(case when pac.code='housing_subsidy' then ssal.validate_date else null end) as x_housing_subsidy_date ,sum(case when pac.code='one_child_allw' then ssal.after_amount else 0 end) as x_one_child_allw ,max(case when pac.code='one_child_allw' then ssal.validate_date else null end) as x_one_child_allw_date ,sum(case when pac.code='double_salary' then ssal.after_amount else 0 end) as x_double_salary ,max(case when pac.code='double_salary' then ssal.validate_date else null end) as x_double_salary_date ,sum(case when pac.code='perf_pay' then ssal.after_amount else 0 end) as x_perf_pay ,max(case when pac.code='perf_pay' then ssal.validate_date else null end) as x_perf_pay_date ,sum(case when pac.code in ('contract_bs','position_allw') then ssal.after_amount else 0 end)*12 as x_curr_salary ,sum(case when pac.code in ('dinner_allw','transportation_allw','talk_fee_allw','housing_subsidy') then ssal.after_amount else 0 end) as x_total_allowances ,sum(case when pac.code in ('contract_bs','position_allw','dinner_allw','transportation_allw','talk_fee_allw','housing_subsidy','one_child_allw','double_salary','perf_pay') then ssal.after_amount else 0 end) as x_total_comp ,40 as x_scheduled_weekly_hours ,max(rc.name) as x_currency ,max(rjt.transfer_date) as x_job_transfer_date from single_salary_adjustment ssa full join single_salary_adjustment_line ssal on ssal.adjustment_id=ssa.id full join hr_employee he on ssa.employee_id=he.id full join payroll_archive_config pac on pac.id=ssal.archive_config_name full join res_currency rc on rc.id=ssal.currency full join rank_job_transfer rjt on rjt.employee_id=he.id where ((ssal.validate_to ISNULL or ssal.validate_to>=now()) and ssa.state='done') or rjt.state='done' GROUP BY x_employee_id