Solved

Produce Exception list

Posted on 2013-10-25
4
265 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 48

Assisted Solution

by:Dale Fye
Dale Fye 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

617 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