Searching and Calculating Minimum Years of experience

[michelle1404]

I am trying to select all the employees who have Minimum average years of experience of 3 years.

If an employee worked for 24 years  in 3 companies the average years of experience is 8 years. For this i got result by doing like this 

SELECT AVG(DATEDIFF( IF(leaving_date is null or leaving_date = '' or leaving_date='0000-00-00', CURRENT_DATE(), leaving_date), joining_date )) AS Days FROM employee_deatils WHERE eemp_id=? 

Now while searching of those employees who have worked minimum 3 years (avg) in a company, i am not able to get the results. When i run this query in phpmyadmin,

SELECT e.fname, e.lname, ROUND(SUM(DATEDIFF( IF(ed.leaving_date is null or ed.leaving_date = '' or ed.leaving_date='0000-00-00', CURRENT_DATE(), ed.leaving_date), ed.joining_date ))/365,0) AS Diff from employees e INNER JOIN employee_deatils ed ON e.id=ed.eemp_id group by ed.eemp_id

it shows 2 records one with 0 and 3 years. But how to compare this with entered minimum years i am not getting. 

Not getting where to put Where condition with =3 years

[requinix]

So "average years of experience per company worked for". Just so we're clear here what it is you're averaging. Because average is about a ratio: some number per some other number.

You have a query that can give you the average for an employee. Your first step should be to adapt that so you can see all employees and their averages. You've somewhat done that in your second query except you added a bunch of other stuff to it which may or may not be useful.

So back up a step. Can you write a query that just gives you the (let's say) eemp_id and the AVG(...) of their work experience? It should look very similar to the first query.

[michelle1404]

Yes, This is the query 

SELECT eemp_id, AVG(DATEDIFF( IF(leaving_date is null or leaving_date = '' or leaving_date='0000-00-00', CURRENT_DATE(), leaving_date), joining_date )) AS Days FROM employee_deatils group by eemp_id

 

[requinix]

Great, but if you want years then you'll need the /365. And think about whether you want to round 2.5 years of experience up to 3 or not - because that's what a ROUND() would do.

That gives you the ID and the average experience. If you want to only return IDs that have enough experience, add a HAVING clause to your query:

...HAVING Days >= 1095

/* or, if you /365 and alias the value as Years, */
...HAVING Years >= 3

I assume you also want the first and last names? JOIN in the employees table based on the ID and you should be set.

[Barand]

Use DATE type columns for your dates, not varchar. Have your leaving dates either a valid date or NULL.

SELECT eemp_id
     , fname
     , lname
     , AVG(timestampdiff(MONTH, joining_date, coalesce(leaving_date, curdate()))) as av_mths
FROM employee_details ed
     JOIN
     employee e ON e.empid = ed.eemp_id
GROUP BY eemp_id
HAVING av_mths >= 36;