Need to pull records from a database where users have multiple entries

Question

I'm working with a really simple query that pulls data to show all visitors to a certain location within a given time frame.

SELECT VisitID, UserID, UserName, VisitDate
From Visit
WHERE VisitDate BETWEEN '04/01/2016' AND '04/03/2016'

And of course I get results like so:

VisitID     UserID     UserName         VisitDate
----------------------------------------------------
0001        1234       John Smith       04/01/2016
0002        3456       Jane Doe         04/01/2016
0003        6789       Mike Jones       04/02/2016
0004        1234       John Smith       04/02/2016
0005        4567       Bob Brown        04/02/2016
0006        3456       Jane Doe         04/03/2016
0007        1234       John Smith       04/03/2016

Now what I need to do is only select the users who had 2 or more visits during the given time period, so in this particular example, I wouldn't want Mike Jones and Bob Brown because they only had 1 visit. I'm not entirely sure how to go about this. I was thinking it would be something along the lines of declaring a variable, setting that variable to equal a COUNTof VisitID, and then adding a WHERE clause with COUNT(VisitID) >= 2. But I'm not sure about the syntax. Am I on the right track? How would I filter these results by only visitors with multiple visits?

Just wrap the query in another query and group by user `having count(*) > 1` — Charleh, Apr 15 '16 at 18:58

score 4 · Accepted Answer · answered Apr 15 '16 at 19:00

4

If I'am understand you right, you need this:

SELECT UserID, UserName
FROM Visit
WHERE VisitDate BETWEEN '04/01/2016' AND '04/03/2016'
GROUP BY UserID, UserName
HAVING COUNT(*) >= 2

answered Apr 15 '16 at 19:00

OlegAxenow

380
2
5

1

This only works if he doesn't require the VisitID/Visit Date returned, in which case, adding those fields will break this. – Dave C Apr 15 '16 at 19:15
@JiggsJedi While that's true, he explicitly states that he wants the "users who had 2 or more visits during the given time period," so this answer absolutely answers the question correctly. I upvoted your comment, though, because it adds clarity for the future. – Stan Shaw Apr 15 '16 at 19:24

Dave C · Answer 2 · 2016-04-15T19:31:53.887

0

I prefer to use a windowed function in a CTE for this sort of thing:

;WITH CTE AS
(
     SELECT VisitID, UserID, UserName, VisitDate,
            COUNT(2) OVER(PARTITION BY UserID) AS countVal
     From Visit
     WHERE VisitDate BETWEEN '04/01/2016' AND '04/03/2016'
)

SELECT *
FROM CTE
WHERE countVal>=2

Per Stan's comments -- If you only want distinct UserName/UserID values, change the final SELECT to this:

SELECT DISTINCT UserID, UserName
FROM CTE
WHERE countVal>=2

edited Apr 15 '16 at 19:31

answered Apr 15 '16 at 19:09

Dave C

7,272
1
19
30

This will return duplicate records for users having multiple visits. Therefore, his application would need to remove the duplicates, which is a transfer of logical responsibility from the query. If his question was phrased differently ("I want all of the VISITS for all of the users who had multiple visits in a given date range), I think this would be an appropriate answer. – Stan Shaw Apr 15 '16 at 19:25
Not quite true duplicates because of the visitID/Date -- but you're 100% right, the userID/Name would be duplicated for each visit listed. I had assumed he wanted dates based on his sample query -- I guess some clarification was needed :) I'll update my answer to cover off this hiccup. – Dave C Apr 15 '16 at 19:29

Need to pull records from a database where users have multiple entries

2 Answers2