We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

List records where values do not exist

Medium Priority
193 Views
Last Modified: 2013-11-26
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

Thanks!
select number,hoursperperiod,name,paycode
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

Open in new window

KronosQuerySample.xls
Comment
Watch Question

Commented:
I have not seen the data but I think this should do it:
select 	number,
		hoursperperiod,
		name,
		paycode
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')
		and paycode not in ('ABS', 'SICK', 'UNPDSICK','SUSPEND')
group by number,hoursperperiod,name,paycode
order by paycode

Open in new window

Author

Commented:
Hi - I tried that, but it lists the employee anyhow for all the other paycodes that exist for them.  I need the employee excluded all together if they have one of those qualifying paycodes.  
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Perfect - Thank you!!!

Commented:
No problem!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.