Solved

employee exception report in SQL

Posted on 2009-04-07
3
229 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

867 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

22 Experts available now in Live!

Get 1:1 Help Now