Tim Tim - 8 months ago 68
SQL Question

Extracting Unknown Dates

I have two tables, Employees and Job_History. The two tables have three columns that are identical: employee_id, job_id, and department_id.

I need to display the last_name and department_id from the Employees table, as well as the date when each employee hits their 20 year work anniversary.

The date when the employees start their work is located in start_date from the Job_History table. The anniversary date needs to be displayed like the format: January twenty-third, Nineteen Eighty-Two.

select e.last_name, e.department_id, count(*) as "20 Year Anniversary"
from job_history l
inner join employees e on e.department_id = l.department_id
group by e.department_id, e.last_name
having count(*) = l.start_date + 20;

Now when I run it, it tells me this isn't a group function. How would I change it to the proper syntax?


Not sure why you would be counting anything, never mind comparing a count to a date. Adding 20 to a date adds twenty days.

This might be closer to what you want:

select e.last_name, e.department_id,
  add_months(min(jh.start_date), 240) as "20 Year Anniversary"
from employees e
inner join job_history jh
on jh.employee_id = e.employee_id
group by e.department_id, e.last_name;

The join between the tables is only on emoloyee_id, because you want the earliest start date of any job in any department (presumably).

Once you have the earliest date you can add 20 years, which is 240 months.

You can read about how to format a date as a string in the documentation.