SQL - How to return records only when both genders are present in the same job title?

Vote count:

0

Okay, so I've got a SQL query that is pulling employee information; job title, gender, pay rate, etc. I have a temp table present and am currently showing the average Pay Rate. Great.

Now I need to bring in the Job Title and keep the average pay rate but only limit the results where both Males and Females are present in the Job Title. Help?

All help is great appreciated!

Also, Is there anyway to create a new column that shows the difference in pay between Males and Females?

If EXISTS (Select * FROM TempDB..SYSOBJECTS WHERE name =  '##JTemp')
BEGIN
DROP TABLE ##JTemp
END
GO

Select
e.EmployeeID AS 'Employee ID',
c.FirstName + ' ' + c.LastName AS 'Employee Name',
e.Gender AS 'Employee Gender',
e.Title AS 'Job Title',
edh.DepartmentID,
d.Name AS 'Department Name',
MAX(eph.Rate) AS 'Pay Rate',
eph.PayFrequency AS 'Pay Frequency',
MAX(eph.Rate) * eph.PayFrequency AS 'Rate x Frequency'

Into ##JTemp

From
HumanResources.Employee e
INNER JOIN Person.Contact c on c.ContactID = e.EmployeeID
INNER JOIN HumanResources.EmployeeDepartmentHistory edh on            
edh.EmployeeID = e.EmployeeID
INNER JOIN HumanResources.Department d on d.DepartmentID = edh.DepartmentID
INNER JOIN HumanResources.EmployeePayHistory eph on eph.EmployeeID = 

e.EmployeeID

Where
edh.EndDate is NULL

Group By
e.EmployeeID,
c.FirstName,
c.LastName,
e.Gender,
e.Title,
edh.DepartmentID,
d.Name,
eph.PayFrequency

Select
[Job Title],
[Employee Gender],
AVG([Pay Rate]) AS 'Average Pay Rate',
AVG([Rate x Frequency]) AS 'Average Rate x Frequency'

From ##JTemp

Group By
[Job Title],
[Employee Gender]

Order By
[Job Title]

--Where [Employee Gender] = 'M'

IF EXISTS (Select * FROM TEMPDB..SYSOBJECTS WHERE name = '##JTemp')
BEGIN
DROP TABLE ##JTemp
END
GO

asked 2 mins ago

Ken Wright

15

SQL - How to return records only when both genders are present in the same job title?