Solved

Produce Exception list

Posted on 2013-10-25
4
259 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 40

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 76

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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 …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now