JFrame JFrame - 2 months ago 7
SQL Question

Selecting multiple unique matches from one column that match another column

I have a list of codes (101, 102, 103, 104) and I want to pick out the people in the following table that have two or more different codes from the list occurring within a year of each other.

Name Code1 Code1date
John 101 01/01/2016
John 102 01/02/2013
Chris 101 01/01/2015
Chris 101 01/05/2014
Chris 102 01/10/2015
Mark 101 01/11/2011
Mark 101 01/01/2011
Mark 107 01/07/2012


So in this sample only Chris would be selected because he has a 101 code and a 102 code within a year of each other.

Thanks!

Answer

Try with the below query if you are using SQL Server.

SELECT name
FROM yourtable
WHERE code1 in (101, 102, 103, 104)
GROUP BY name, year(code1date)
HAVING COUNT(distinct code1) > 1