Solved

List records where values do not exist

Posted on 2009-07-10
5
169 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
0
Comment
Question by:palmer9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24823541
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

0
 

Author Comment

by:palmer9
ID: 24823570
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.  
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24823598
okay.. I see. Try this:
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 empnum not in ( select 	number
							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 IN ('ABS', 'SICK', 'UNPDSICK','SUSPEND')
							)
group by number,hoursperperiod,name,paycode
order by paycode

Open in new window

0
 

Author Closing Comment

by:palmer9
ID: 31602073
Perfect - Thank you!!!
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24823637
No problem!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Please explain purpose of GZIP 4 56
SSIS GUID Variable 2 31
Activating .NET Framework 3.5 and which option to choose 5 25
denied execute as 13 24
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question