Solved

employee exception report in SQL

Posted on 2009-04-07
3
228 Views
Last Modified: 2012-05-06
im trying to create a query/report that will generate a report detailing all the employees who forgot to enter their time on friday

my thought was ...

<pseduocode>
"select * from tracking table where hours IS NULL and (TrackDate >= '04/06/2009') AND (TrackDate <= '04/10/2009')"
</pseduocode>

but if they didnt enter any data then there is no record of them anyway.

how do i need to construct my logic to get the data i need?

two queries : one that says who DID enter time and then compare a list of the total employees

any assistance is appreciated.

thx



0
Comment
Question by:nriddock
3 Comments
 
LVL 9

Expert Comment

by:tl121000
ID: 24088519
Do you have an employee table?
If so, you can run a correlated subquery checking the employee id table and then checking the tracking tbale where the emplyeed ID not exist.
 
 
 
0
 

Author Comment

by:nriddock
ID: 24088602
emp table - yes

so....

select * from EmpTable, TrackingTable

where EmpTable.EmpID = TrackingTable.EmpID

(INSERT SUBQUERY HERE?)


suggestions?
0
 
LVL 25

Accepted Solution

by:
reb73 earned 500 total points
ID: 24088898
Here you go -

(just expanding on tl121000's original post 24088519 to give you the relevant code)
select * from EmpTable E

where not exists (select 1 from TrackingTable T where T.EmpId = E.EmpId AND TrackDate >= '04/06/2009' AND TrackDate <= '04/10/2009')

Open in new window

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

706 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

16 Experts available now in Live!

Get 1:1 Help Now