Different from dates

Question

1. what is the difference between from date and hire date.
2. Why do i get a different answer when i use from_date in the salaries table instead of from_date from the dept_employees table?

SELECT
    dept_name, d.dept_no, AVG(salary) AS salary
FROM
    t_salaries t
        JOIN
    t_dept_emp de ON t.emp_no = de.emp_no
        JOIN
    t_departments d ON d.dept_no = de.dept_no
WHERE
    t.from_date BETWEEN '1999-01-01' AND '1999-12-31'
GROUP BY dept_no
ORDER BY dept_no
;

Answer 1

Hi Pius!
Thanks for reaching out.

The hire_date column indicates the date that the employee has started working. The from_date and to_date columns indicate the start date and the end date for the current work that the employee has worked. Let's check for emp_no=10001. If we run the following query:
SELECT
*
FROM
employees
WHERE emp_no = 10001;
we see Georgi Facello has started working at 1986-06-26. This is his career start for the company. But in this company he has worked at different positions during different periods. If we run the following query:
SELECT
*
FROM
salaries
WHERE emp_no = 10001;
we see all different periods of work with all salaries and the columns from_date and to_date for all his previous jobs. If he is currently working at a particular position the date is set to '9999-01-01'.
The different answer comes from the different records that each table contains for this particular column. You can check this with the SELECT statement comparing the result sets of the queries which select this column from these tables.

Hope this helps.
Best,
Tsvetelin

Different from dates

Submit an answer

related questions