Solved

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

Posted on 2013-12-04
9
483 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
[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
9 Comments
 
LVL 32

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 35

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
LVL 35

Expert Comment

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

Assisted Solution

by:Scott Pletcher
Scott Pletcher 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 110

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

733 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