05 Dec 2022

Posted on:

14 Mar 2022

0

# why did we used the active column

why did we  use active column instead of choosing count(emo_no)

5 answers ( 0 marked as helpful)
Instructor
Posted on:

24 Mar 2022

0

Hi Doaa!

Thanks for reaching out.

We want to show if a certain manager has been active in each year after 1990. Therefore, we are applying a condition that we want to be satisfied.
COUNT(emp_no), instead, would count the number of all employees present in a certain table (or join of tables).
To solve the given SQL-Tableau task, we need the first piece of information. That's why we create the active column.

Hope this helps.
Best,
Martin

Posted on:

09 Nov 2022

1

SELECT
d.dept_name,
ee.gender,
dm.emp_no,
dm.from_date,
dm.to_date,
e.calendar_year,
CASE
WHEN YEAR(dm.to_date) >= e.calendar_year AND YEAR(dm.from_date) <= e.calendar_year THEN 1
ELSE 0
END AS active
FROM
(SELECT
YEAR(hire_date) AS calendar_year
FROM
t_employees
GROUP BY calendar_year) e
CROSS JOIN
t_dept_manager dm
JOIN
t_departments d ON dm.dept_no = d.dept_no
JOIN
t_employees ee ON dm.emp_no = ee.emp_no
ORDER BY dm.emp_no, calendar_year;

Instructor
Posted on:

11 Nov 2022

0

Hi Abdul!
Thanks for reaching out.

Could you please explain what exactly the question is? This can help us assist you better. Thank you.

Best,
Tsvetelin

Posted on:

20 Nov 2022

0

SELECT
d.dept_name,
ee.gender,
dm.emp_no,
dm.from_date,
dm.to_date,
e.calendar_year,
CASE
WHEN YEAR(dm.to_date) >= e.calendar_year AND YEAR(dm.from_date) <= e.calendar_year THEN 1
ELSE 0
END AS active
FROM
(SELECT
YEAR(hire_date) AS calendar_year
FROM
t_employees
GROUP BY calendar_year) e
CROSS JOIN
t_dept_manager dm
JOIN
t_departments d ON dm.dept_no = d.dept_no
JOIN
t_employees ee ON dm.emp_no = ee.emp_no
ORDER BY dm.emp_no, calendar_year;

Instructor
Posted on:

05 Dec 2022

0

Hi rouaa!

Thanks for reaching out!

Can you please add a question to the code you've provided, so that we can assist you better? Thank you.