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:
select year(hire_date) as calendar_year from t_employees
select year(from_date) from t_dept_emp
select year(to_date) from t_dept_emp
select cy.calendar_year, e.gender, d.dept_name, avg(s.salary) as average_salary,
when cy.calendar_year between de.from_date and de.to_date then 1
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
Thanks for reaching out.
There is a slight discrepancy between what you've described and the code you've provided. Let's please clarify it so that we can assist you better.
You say that your connection is lost when you try to create the active column, while you only have active_in_department in the code below. Therefore, is this the code that, upon execution, makes you lose your connection ot the server, or is there another code? ... Because as I execute the code you've provided, I realise there must have been another piece of code you've executed in advance to create the active column.
Can you please share this bit of code with us? Thank you.
Looking forward to your answer.