?
Solved

SQL Statememt

Posted on 2011-09-11
6
Medium Priority
?
432 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this article, we’ll look at how to deploy ProxySQL.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
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…

752 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