# Searching and Calculating Minimum Years of experience

Go to solution Solved by Barand,

## Recommended Posts

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

##### Share on other sites

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.

##### Share on other sites

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`

##### Share on other sites

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.

##### Share on other sites

• Solution

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;```

• 1
• 1
• 1

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.