[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Produce Exception list

•      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
lbarnett419
Asked:
lbarnett419
2 Solutions
 
SharathData EngineerCommented:
Can you provide some sample data?
0
 
Dale FyeCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
lbarnett419Author Commented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now