Solved

employee exception report in SQL

Posted on 2009-04-07

1 solution

Medium Priority

233 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

Comment

Question by:nriddock

[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

Join The Community

3 Comments

LVL 9

Expert Comment

by:tl121000

ID: 240885192009-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.

Author Comment

by:nriddock

ID: 240886022009-04-07

emp table - yes

so....

select * from EmpTable, TrackingTable

where EmpTable.EmpID = TrackingTable.EmpID

(INSERT SUBQUERY HERE?)

suggestions?

LVL 25

Accepted Solution

by:reb73

reb73 earned 2000 total points

ID: 240888982009-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

Featured Post

Question has a verified solution.

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

5 Lessons the Delta Outage Should Teach Us About Datacenter Security and Disaster Prevention

Article by: Concerto Cloud

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…

Installing SQL Server vNext on Linux: Part 2

Article by: Yashwant

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.

Executing a SQL Script from a Unix Shell Script and Passing Parameters

Video by: Steve

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.