Solved

List records where values do not exist

Posted on 2009-07-10
5
165 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
  • 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now