?
Solved

SQL Statememt

Posted on 2011-09-11
6
Medium Priority
?
437 Views
Last Modified: 2012-08-14
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
Comment
Question by:Rowley4
  • 2
  • 2
  • 2
6 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 36519357
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36519360
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 Comment

by:Rowley4
ID: 36519369
Yes I am looking for different answers. Different questions entirely. Thank you MWVISA1!
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Closing Comment

by:Rowley4
ID: 36519371
This was a huge help!! Thank you so much!! I was absolutely NOT in the right ball park with this one!
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36519387
Glad to help!

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

Expert Comment

by:Kevin Cross
ID: 36519412
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
What we learned in Webroot's webinar on multi-vector protection.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question