The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

Problem 2- Solution in SQL

Problem 2- Solution in SQL

0
Votes
3
Answer

Hello everyone!
In the  solution of the second problem, when we select calendar_year, why we use a subquery in the from statement instead of using year(hire_date) in the select statement.  I tried it and didn’t work.
Best,
Mohammed B,

3 Answers

365 Team
0
Votes

Hi Bouchoucha!
Thanks for reaching out.
Can you please support your question with the exact query you’ve executed? This will help us assist you better. 
Looking forward to your answer.
Best,
Martin

0
Votes

This is the query i’ve executed
SELECT
d.dept_name,
e.gender,
dm.emp_no,
dm.from_date,
dm.to_date,
YEAR(e.hire_date) AS calendar_year,

CASE
WHEN YEAR(dm.to_date) >= calendar_year AND YEAR(dm.from_date) <= calendar_year THEN 1
ELSE 0
END AS active

FROM
t_dept_manager dm
JOIN
t_departments d ON dm.dept_no = d.dept_no
JOIN
t_employees e ON dm.emp_no = e.emp_no

ORDER BY dm.emp_no, calendar_year;

365 Team
0
Votes

Hi Bouchoucha!
Thank you very much for your reply.
What is important to remember is that we cannot set alias names in the field list and use them in the calculation/formula for defining another field. This must happen, say, at a different level in our query.
Since we want to use the calendar_year value in the CASE statement, we need to have defined it with the FROM clause (as opposed to the SELECT statement). Hence, the following subquery is very important to be added in the FROM clause.

    (SELECT 
        YEAR(hire_date) AS calendar_year
    FROM
        t_employees
    GROUP BY calendar_year) e

Please add it and retry running your query.
Hope this helps.
Best,
Martin

Thank you so much Martin

3 weeks

You are very welcome!

2 weeks