MySQL Forums
Forum List  »  General

Comparing a column value to a different column in the following row
Posted by: Kimber Barton
Date: February 16, 2024 03:20PM

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,

Options: ReplyQuote

Written By
Comparing a column value to a different column in the following row
February 16, 2024 03:20PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.