Last answered:

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.

Looking forward to your answer.
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.

Looking forward to your answer.
Kind regards,
Martin

Submit an answer