why did we used the active column

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

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;

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

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;

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