select rjt.id as x_rjt_id, rjt.employee_id as x_employee_id, rjt.transfer_date as x_transfer_date, tt.id as x_tt_id, tt.name as x_tt_name, tt.date_start as x_date_start, tt.date_end as x_date_end from rank_job_transfer rjt left join (select id,name,date_start,date_end from payroll_period where date_end= (select max(date_end) from payroll_period where state='open') and state='open') as tt on rjt.transfer_date>=tt.date_start and tt.date_end>=rjt.transfer_date where tt.name is not null