I am not sure what the "new code" added means. This returns exactly same set as Sean but using PIVOT. Just a different approach. I compared the two in Actual Query Execution plan and Sean's had a lower query cost.

SELECT

pvt.Location,

'EXEMPT' as 'Batch ID',

pvt.EmpId as 'File #',

pvt.EmpLastName as 'Last Name',

pvt.EmpFirstName as 'First Name',

'y' as 'Cancel Pay',

NULL as 'Pay #',

NULL as 'Reg Earnings',

2 AS 'Rate Code',

NULL as 'Tax Frequency',

NULL as 'Reg Hours',

'Hours 4 Code' = CASE WHEN S IS NOT NULL

THEN 'S'

ELSE NULL

END,

'Sick Hours' = S,

'Hours 4 Code' = CASE WHEN V IS NOT NULL

THEN 'V'

ELSE NULL

END,

'Vacation Hours' = V,

'Hours 4 Code' = CASE WHEN O IS NOT NULL

THEN 'O'

ELSE NULL

END,

'Unknown Hours' = O

FROM (

SELECT

e.EmpID

,e.Location

,e.EmpFirstName

,e.EmpLastName

,Type = CASE WHEN Type = 'Sick'

THEN 'S'

WHEN Type = 'Vacation'

THEN 'V'

ELSE 'O'

END

,Total_hours = SUM(total_hours)

FROM #tblEmployee e

JOIN #tblTimeOffRequest r

ON e.EmpId = r.EmpId

GROUP BY

e.EmpID

,e.Location

,e.EmpFirstName

,e.EmpLastName

,CASE WHEN Type = 'Sick'

THEN 'S'

WHEN Type = 'Vacation'

THEN 'V'

ELSE 'O'

END

) PersonalTime

PIVOT

( MAX(total_hours) FOR Type IN (, [V],[O])) as pvt