Solved

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

Posted on 2013-12-04
9
482 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 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 34

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 109

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

860 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