Solved

SQL query to find all users with an entry in each of separate date ranges

Posted on 2013-12-04
9
479 Views
Last Modified: 2014-03-28
table 1 has user info, user id, full name, email, etc
table 2 has test info, user id, date of test, q1, q2, etc

user id is the link between tables

I need a query that will give me a list of all the users that took at least 1 test between 10/01/2012-12/31/2012 AND took at least 1 test between 10/01/2013-12/31/2013

mySQL is my database of choice, but this application also runs on some other SQL databases so less DB specific and more generic query would be helpful.

I would really appreciate help with this.
0
Comment
Question by:greglwhite
9 Comments
 
LVL 31

Expert Comment

by:awking00
ID: 39696383
Can user take more than one test on the same day? If so, what distinguishes that?
0
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 125 total points
ID: 39696537
This would be T-SQL, using count
SELECT T1.userid
from table1 T1 --user table
INNER JOIN (SELECT COUNT(*), userid FROM table2 WHERE dateoftest>= '2013-10-01' AND dateoftest < '2014-01-01' GROUP BY userid HAVING COUNT(*) > 0) TEST2013
ON TEST2013.userid = T1.userid
INNER JOIN (SELECT COUNT(*), userid FROM table2 WHERE dateoftest>= '2012-10-01' AND dateoftest < '2013-01-01' GROUP BY userid HAVING COUNT(*) > 0) TEST2012
ON TEST2012.userid = T1.userid

This version uses distinct--the distinct could be done in the test subqueries or the main select (as shown)

SELECT DISTINCT T1.userid
from table1 T1 --user table
INNER JOIN (SELECT userid FROM table2 WHERE dateoftest>= '2013-10-01' AND dateoftest < '2014-01-01'  TEST2013
ON TEST2013.userid = T1.userid
INNER JOIN (SELECT userid FROM table2 WHERE dateoftest>= '2012-10-01' AND dateoftest < '2013-01-01') TEST2012
ON TEST2012.userid = T1.userid
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 125 total points
ID: 39696620
Why do you want to spend all the work doing counts and grouping.  This should work:

SELECT * 
FROM   table1 t1 
WHERE  EXISTS (SELECT 1 
               FROM   table2 t2 
               WHERE  t1.userid = t2.userid 
                      AND t2.date_of_test >= To_date('10012012', 'mmddyyyy') 
                      AND t2.date_of_test < To_date('01012013', 'mmddyyyy')) 
       AND EXISTS (SELECT 1 
                   FROM   table2 t3 
                   WHERE  t1.userid = t3.userid 
                          AND t3.date_of_test >= To_date('10012013', 'mmddyyyy') 
                          AND t3.date_of_test < To_date('01012014', 'mmddyyyy')) 

Open in new window


This should also take advantage of indexes, especially if you have a composite index on TABLE2 that contains both USERID and DATE_OF_TEST.

You would have to modify the date part for different database systems, but the basic syntax should work everywhere.
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 39696650
I provided a couple ideas for the author to consider.  Sorry if I didn't suggest the "only, best, correct" way, which by the way doesn't exist...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:johnsone
ID: 39696709
Sorry.  Not suggesting that your answer is wrong.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
ID: 39697046
Why do you want to spend time doing two separate searches of table2?


SELECT *
FROM table_1
WHERE user_id IN (
SELECT user_id
FROM table_2
WHERE
    (date_of_test >= STR_TO_DATE('10/01/2012', '%m/%d/%Y') AND date_of_test < STR_TO_DATE('01/01/2013', '%m/%d/%Y')) OR
    (date_of_test >= STR_TO_DATE('10/01/2013', '%m/%d/%Y') AND date_of_test < STR_TO_DATE('01/01/2014', '%m/%d/%Y'))
GROUP BY
    user_id
HAVING
    MAX(CASE WHEN (date_of_test >= STR_TO_DATE('10/01/2012', '%m/%d/%Y') AND date_of_test < STR_TO_DATE('01/01/2013', '%m/%d/%Y')) THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN (date_of_test >= STR_TO_DATE('10/01/2013', '%m/%d/%Y') AND date_of_test < STR_TO_DATE('01/01/2014', '%m/%d/%Y')) THEN 1 ELSE 0 END) = 1
)


Note that this method is easily extensible to 3 comparisons, 4 comparisons, 2 out of 3, 3 out of 4, etc..
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 39697121
No worries johnsone, I was probably over-defensive there.
After I wrote the "count" query, I decided that I would probably not use that method either...so I wrote the second query using distinct and inner joins which I like better.  Of course, performance and the specific context of the need will probably determine the "best" method.
Hopefully someone's query will work well for the author.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39703094
If you have access to a scripting language you can probably make this simpler.  And even if you don't it's wise to choose the ISO-8601 standard date/time representation for internal purposes.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 125 total points
ID: 39726854
Here's another way:

SELECT 
  userId, 
  MAX(testDate BETWEEN '2012-10-01' AND '2012-12-31') AS testedIn2012,
  MAX(testDate BETWEEN '2013-10-01' AND '2012-12-31') AS testedIn2013
FROM table2
WHERE 
  testDate BETWEEN '2012-10-01' AND '2012-12-31' 
  OR testDate BETWEEN '2013-10-01' AND '2012-12-31' 
GROUP BY 
  userId
HAVING
  testedIn2012 AND testedIn2013;

Open in new window

It would actually work without the WHERE clause at all, but using them might make it a bit faster, depending on how many tests there are.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to recover a database from a user managed backup
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

20 Experts available now in Live!

Get 1:1 Help Now