Solved

SQL Statememt

Posted on 2011-09-11
6
430 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 92

Accepted Solution

by:
Patrick Matthews earned 500 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
Independent Software Vendors: 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!

 

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 92

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

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

690 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