Jump to content

Searching and Calculating Minimum Years of experience


michelle1404
 Share

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

Link to comment
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.

Link to comment
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.

Link to comment
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;

 

  • Like 1
  • Thanks 1
  • Great Answer 1
Link to comment
Share on other sites

Join the conversation

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

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.