Find 2 sets of Date Ranges spanning 1 Year

[Unravel]

I have a table with members, referralnumbers and 2 dates: effectivedate and termdate.

At the [highlight #FCE94F]member[/highlight] level, I want to find 2 referralnumbers that together their respective date ranges span 1 year.

For example: Member Jack Beanstalk referralnumber 12345 spans from 02/01/2015-08/31/2015 and referralnumber 67890 spans 09/01/2015-01/31/2016

How do I write this in SQL to find these pairs?

 

[TamarGranor]

I think you'll want to do a self-join, that is joint two instances of your table based on member ID and on the end date of the record in one table being one day less than the start date of the record in the other table.

Tamar