• Status: Solved
• Priority: Medium
• Security: Public
• Views: 442

# SQL Statememt

I need to write an SQL statement to compare the average number of years of experience it takes a male to become manager vs. the average number of years it takes a female to become manager in this company.

* employee (emp_id,f_name,l_name,dob,ssn,gender,hire_date)
* department (dept_id,name)
* emp_in_dept (emp_id,dept_id,begin_date,end_date)
* mgr_of_dept (dept_id,emp_id,begin_date,end_date)
* salary(emp_id,salary,begin_date,end_date)
* job_title(emp_id,title,begin_date,end_date)

I have so far but I am not sure I am in the ball park with this:

SELECT f_name, l_name, gender, hire_date
FROM employee
WHERE mgr_of_dept, begin_date
0
Rowley4
• 2
• 2
• 2
1 Solution

Commented:
Assuming that we can ignore cases where an employee is hired, leaves, and then comes back, this is how it would work in SQL Server.

The basic approach would work for MySQL, but you may need a different function than DATEDIFF:

SELECT e.gender, AVG(DATEDIFF(day, e.hire_date, x.begin_date) / 365.25)
FROM employee e INNER JOIN
(SELECT m.emp_id, MIN(m.begin_date) AS MinDate
FROM mgr_of_dept m
GROUP BY m.emp_id) x ON e.emp_id = x.emp_id
GROUP BY e.gender

There is, of course, one very serious methodological flaw with this approach: by only including the employees that make it to the manager ranks, you are potentially skewing your sample.  For example, suppose that male and female managers both got to the managerial rank after 5 years, but a higher proportion of male employees make it to manager than female employees.  Your average tenure would thus mask a potential bias in favor of the males.

Patrick
0

Chief Technology OfficerCommented:
Rowley4, is this question and http://www.experts-exchange.com/Q_27302231.html meant to be the same, i.e., was the cutoff code in the other what you are showing above. Just trying to figure out if you are looking for different answers before getting started on this.
0

Author Commented:
Yes I am looking for different answers. Different questions entirely. Thank you MWVISA1!
0

Author Commented:
This was a huge help!! Thank you so much!! I was absolutely NOT in the right ball park with this one!
0

Commented:

Just be sure that you read my last note very carefully before you make any inferences based on the result :)
0

Chief Technology OfficerCommented:
Thanks for confirming! For DATEDIFF in MySQL, note that it only takes two parameters and the newer date goes first if you want a positive result. i.e., DATEDIFF(x.begin_date, e.hire_date)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.