select tt.id as x_tt_id ,tt.name as x_tt_name ,hr.id as x_hr_id ,hr.employee_number as x_tt_employee_number ,hr.name as x_employee_name ,hr.hiredate as x_hiredate ,hr.departure_time as x_departure_time ,tt.date_start as x_date_start ,tt.date_end as x_date_end from hr_employee hr 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 hr.hiredate >= tt.date_start and tt.date_end>=hr.hiredate or tt.date_start<=hr.departure_time and hr.departure_time <= tt.date_end where tt.name is not null