Solved

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

Posted on 2013-12-04
9
485 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

627 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