combining result sets and sum of hours 1

[ladyemrys]

Hi!

I'm very new to SQL and have been asked to select Full time employees only (EmployeeTypeId = 1 for Full Time), and have been asked to add a total of Pay hours to the Query below:

SELECT E.BadgeNum, E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName AS Name,
TKP.PayrollCode as PayrollCode, SUM(TKP.PayTime/3600) as 'Pay Hours'
FROM TKPAY TKP, Employees E
WHERE TKP.EmployeeId = E.EmployeeId


GROUP BY E.BadgeNum, E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName, TKP.PayrollCode

UNION

SELECT E.BadgeNum, E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName AS Name,
TKPS.PayrollCode as PayrollCode, SUM(TKPS.PayTime/3600) as 'Pay Hours'

FROM TkPaySupplementary TKPS, Employees E
WHERE TKPS.EmployeeId = E.EmployeeId


GROUP BY E.BadgeNum, E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName, TKPS.PayrollCode


ORDER BY 2, 1, 3

*********

I added this:

SELECT EmployeeTypeId as 'Full Time'
FROM EmployeeStatus
WHERE EmployeeStatus.EmployeeId = EmployeeId
And EmployeeTypeId = '1'

But get two result sets and was wondering how i could combine them and how i could total the hours. I tried using "with cube" but it totaled each set of "Pay Hours" from the queries.

Any advice is much appreciated! Thank you!
LE

 

[markros]

Try this query:

SELECT E.BadgeNum, E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName AS Name,
       TKP.PayrollCode as PayrollCode, SUM(TKP.PayTime/3600) as 'Pay Hours' 
 FROM  TKPAY TKP inner join Employees E
 ON TKP.EmployeeId = E.EmployeeId
 where exists (select 1 from EmployeeStatus S where S.EmployeeID = E.EmployeeID and S.EmployeeTypeID = '1')
   

GROUP BY E.BadgeNum,  E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName, TKP.PayrollCode

UNION

SELECT E.BadgeNum, E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName AS Name,
       TKPS.PayrollCode as PayrollCode, SUM(TKPS.PayTime/3600) as 'Pay Hours'

 FROM TkPaySupplementary TKPS
 inner join Employees E
 ON TKP.EmployeeId = E.EmployeeId
 where exists (select 1 from EmployeeStatus S where S.EmployeeID = E.EmployeeID and S.EmployeeTypeID = '1')  

GROUP BY E.BadgeNum, E.LastName + ', ' + E.FirstName + ' ' + E.MiddleName, TKPS.PayrollCode


ORDER BY 2, 1, 3

I changed implicit join to explicit JOIN and also added a check for full time employees using EXISTS subquery.

PluralSight Learning Library

 

[ladyemrys]

Thanks! I'm giving it a try right now

 

[ladyemrys]

wow that worked great, thanks! my hours seem a bit weird but i can figure that part out!!!