Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-12-04
9
Medium Priority
?
490 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 500 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 500 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 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 111

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 500 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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

926 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