T-SQL group by from multiple years

Question

I barely know how to ask this question aside from the specific example, so here goes:

We have an event registration table, and I want to match registrants that have registered for one of 4 events in each of the preceding 5 years.

The only way I can think of doing this is with verbose sub-queries, but performance-wise it's an absolute dog:

SELECT FirstName, LastName, EmailAddress 
FROM RegTable
WHERE EventId IN (1,2,3,4)
AND EventYear = 2011
AND FirstName + LastName + DOB IN (SELECT FirstName + LastName + DOB FROM RegTable WHERE EventId IN (1,2,3,4) AND EventYear = 2012)

And so on for each year. Like I said, not very eloquent or efficient.

Is there a simpler way?

ughai · Accepted Answer · 2015-04-30 07:29:03Z

1

You can do a GROUP BY with HAVING and then do a INTERSECT with current Year events

SELECT FirstName, LastName, DOB
FROM RegTable
WHERE EventId IN (1,2,3,4)
AND EventYear IN (2011,2010,2009,2008,2007)
GROUP BY FirstName, LastName, DOB
HAVING COUNT(Distinct EventYear) = 5

INTERSECT

SELECT DISTINCT FirstName ,LastName ,DOB
FROM RegTable 
WHERE EventId IN (1,2,3,4) 
AND EventYear = 2012

The above query in action with sample data. SQL Fiddle

edited Apr 30, 2015 at 7:29

answered Apr 30, 2015 at 7:21

ughai

9,9004 gold badges33 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason RWD Over a year ago

This was it, thanks. It was the 'HAVING COUNT(Distinct EventYear) = 5' that actually achieves what I was after, which was a unique registration in each of the five years specified in the 'EventYear IN' clause. Very much appreciated.

Stefan Steinegger · Accepted Answer · 2015-04-30 07:21:39Z

0

I hoestly didn't understand the question, just rewriting your query (assuming that it is doing what you need):

SELECT RT2011.FirstName, RT2011.LastName, RT2011.EmailAddress 
FROM 
  RegTable RT2011, 
  RegTable RT2012
WHERE RT2011.EventId IN (1,2,3,4)
  AND RT2012.EventId IN (1,2,3,4)
  AND RT2011.EventYear = 2011
  AND RT2012.EventYear = 2012
  AND RT2011.FirstName = RT2012.FirstName
  AND RT2011.LastName = RT2012.LastName
  AND RT2011.DOB = RT2012.DOB

answered Apr 30, 2015 at 7:21

Stefan Steinegger

64.8k17 gold badges132 silver badges193 bronze badges

Collectives™ on Stack Overflow

T-SQL group by from multiple years

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related