Solved

Produce Exception list

Posted on 2013-10-25
4
264 Views
Last Modified: 2013-10-25
•      Table has names & sign in dates (8/1/2013 thru today).

•      I need to determine exceptions within a date range, such as 10/1/2013 thru 10/15/2013--who did not sign in on a certain date within that date range. No record is created if person did not sign in on a certain date.

•      Results should return name & date not signed in.
o      John Smith, 10/11/2013
o      Mary Jones 10/2/2013

•      I’ve tried lots of things, obviously incorrect.
Also need to see the query in Oracle SQL and Access 2007 SQL please!
0
Comment
Question by:lbarnett419
[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
4 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39601018
Can you provide some sample data?
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
ID: 39601036
I would generally start out with two queries, one for all the DISTINCT names, the other for all of the dates in your date range.

I would then create a cartesian join of those two table so that I have a list of all possible people/date combinations

SELECT qry_Names.Person, qry_DatesInRange.SomeDate
FROM qry_Names, qry_DatesInRange

Save this a qry_PersonDates

Finally, create another query that joins those to your sign-in table:

SELECT qry_PersonDates.Person, qry_PersonDates.SomeDate
FROM qry_PersonDates
LEFT JOIN tbl_SignIn
ON qry_PersonDates.Person = tbl_SignIn.Person
AND qry_PersonDates.SomeDate = tbl_SignIn.SignInDate
WHERE tbl_SignIn.ID IS NULL
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 450 total points
ID: 39601056
Along the lines of the above post, generate ALL possibilities of users and dates between the range, then MINUS from actual data.

Here is an Oracle example using the date range of 10/1/2013 to 10/4/2013.

drop table tab1 purge;
create table tab1(name varchar2(20), login date);

--Bill signed in all 4 days
insert into tab1 values('Bill',to_date('10/01/2013','MM/DD/YYYY'));
insert into tab1 values('Bill',to_date('10/02/2013','MM/DD/YYYY'));
insert into tab1 values('Bill',to_date('10/03/2013','MM/DD/YYYY'));
insert into tab1 values('Bill',to_date('10/04/2013','MM/DD/YYYY'));

--Bob didn't sign in on the 3rd
insert into tab1 values('Bob',to_date('10/01/2013','MM/DD/YYYY'));
insert into tab1 values('Bob',to_date('10/02/2013','MM/DD/YYYY'));
insert into tab1 values('Bob',to_date('10/04/2013','MM/DD/YYYY'));

--Biff didn't sign in on the 2nd
insert into tab1 values('Biff',to_date('10/01/2013','MM/DD/YYYY'));
insert into tab1 values('Biff',to_date('10/03/2013','MM/DD/YYYY'));
insert into tab1 values('Biff',to_date('10/04/2013','MM/DD/YYYY'));
commit;

with mydata as (
	select beg_date+(level-1) mydate from
	(
		select to_date('10/1/2013','MM/DD/YYYY') beg_date, to_date('10/4/2013','MM/DD/YYYY') end_date from dual
	)
	connect by level <= (end_date-beg_date)+1
),
distinct_users as (
select distinct name from tab1
)
select name, mydate from mydata,distinct_users
minus
select name, login from tab1
/

Open in new window

0
 

Author Closing Comment

by:lbarnett419
ID: 39601442
Sharath: the results are the same as my queries...no records were returned but thank you for returning with a quick answer.

Slightwv: Yours works using the same concepts but with my tables.

Thanks!
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 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