Experts Exchange connects you with the people and services you need so you can get back to work.

employee exception report in SQL

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

nriddock

Asked: 2009-04-07

Advertise Here

1 Solution

LVL 9

tl121000Commented: 2009-04-07

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.

nriddockAuthor Commented: 2009-04-07

emp table - yes

so....

select * from EmpTable, TrackingTable

where EmpTable.EmpID = TrackingTable.EmpID

(INSERT SUBQUERY HERE?)

suggestions?

LVL 25

reb73Commented: 2009-04-07

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

employee exception report in SQL

Join & Write a Comment Already a member? Login.

Featured Post