Change in departments

To account for the change such as when the employee might move from the current department to the other, I tried creating an 'active' col. The results says lost connection. I used calendar year by the union of all the years just to compare if any year fields might be missing for some tables.  Can you help me to point out what might be redundant here:

with calendar_years
as
(
select year(hire_date) as calendar_year from t_employees
union
select year(from_date) from t_dept_emp
union
select year(to_date) from t_dept_emp
)

select cy.calendar_year, e.gender, d.dept_name, avg(s.salary) as average_salary,
case
when cy.calendar_year between de.from_date and de.to_date then 1
    else 0
end as active_in_department
from calendar_years cy
cross join t_employees e
join t_dept_emp de on e.emp_no = de.emp_no
join t_departments d on de.dept_no = d.dept_no
join t_salaries s on de.emp_no = s.emp_no
where active_in_department = 1
group by cy.calendar_year, e.gender, de.dept_no
having cy.calendar_year <= 2002
order by cy.calendar_year