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
select m.[Job Title], m.ap, m.mp, f.ap, f.mp
from ( Select [Job Title], AVG([Pay Rate]) as ap, max([Pay Rate]) as mp
From ##JTemp
Where [Employee Gender] = 'M'
Group By [Job Title] ) m
join ( Select [Job Title], AVG([Pay Rate]) as ap, max([Pay Rate]) as mp
From ##JTemp
Where [Employee Gender] = 'F'
Group By [Job Title] ) f
on m.[Job Title] = f.[Job Title]