Last answered:

18 Dec 2022

Posted on:

02 Nov 2022


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
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
    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

1 answers ( 0 marked as helpful)
Posted on:

18 Dec 2022


Hi Avash!

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.

Submit an answer