Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Produce Exception list

Posted on 2013-10-25
4
Medium Priority
?
267 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
4 Comments
 
LVL 41

Expert Comment

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

Assisted Solution

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

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1800 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

926 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