I have the following query that lists all employees for a time frame and all the paycodes for each employee. What I need to return is only the employees that do not have certain paycodes in that timeframe. We are basically looking for any employee that was not out sick for a 6 month period. All I need returned is the number,name, and hoursperperiod. I'm attachching a spreadsheet of what the data looks like now, along with my query. I am writing in Visual Studio 2005, and we have SQL Server 2005.
If an employee has any of the following paycodes, I do not want their name to appear in the result set: ABS, SICK, UNPDSICK,SUSPEND
from payrollview.dbo.ppperemployeefile p inner join kronos.dbo.hoursbyday k on
k.empnum = p.number inner join payrollview.dbo.ppperemppositionfields e on e.employeeppper = p.employeeppper
where (hoursperperiod between '48' and '80') and (date between '01/01/2009' and '06/30/2009')
group by number,hoursperperiod,name,paycode
order by paycode