JFrame JFrame - 1 year ago 81
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.


Answer Source

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

FROM yourtable
WHERE code1 in (101, 102, 103, 104)
GROUP BY name, year(code1date)
HAVING COUNT(distinct code1) > 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download