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

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)
    * address (emp_id,street,city,state,zip,country,phone)
    * 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
Asked:
Rowley4
  • 2
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
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

Open in new window


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
 
Kevin CrossChief 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
 
Rowley4Author Commented:
Yes I am looking for different answers. Different questions entirely. Thank you MWVISA1!
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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

Just be sure that you read my last note very carefully before you make any inferences based on the result :)
0
 
Kevin CrossChief 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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now