Comparing a column value to a different column in the following row

[Kimber Barton]

Hi,
I am trying to run some tests on data I have received - historically it comes from an unreliable source so I am checking EVERYTHING. I'm getting stuck on something that it seems like should be pretty simple, and that is to compare one column in one row with a different column in another row.

As an example, the table has the following columns (other than the primary key and other tracking type columns that I add):
Member ID
An effective date
a Term date
A physician's ID

What I want to do is validate that none of the members have rows where the span between the effective date and the term date overlap another row's effective date and term date (for the same member).

For example, I want to locate members that have this type of situation where the two rows time period overlap:
Member ID Effective Date Term Date Physician ID
1234 1/1/2020 12/31/2020 ABCD
1234 8/1/2020 3/31/2021 CDKB

It just so happens that right now that the most rows any one member has is two, but that could change in the future.

Thanks in advance,
Kimber