Compare dates between 2 different rows and columns

Question

I'm having problems figuring this out. Here is an example table:

What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.

How do I do this?

Answer 1

Judging from the screenshot, it looks like SQL Server. So this should work:

SELECT * FROM myTable
WHERE DISCONTINUE_DATE > (SELECT TOP 1 EFFECTIVE_DATE 
  FROM myTable AS sub 
  WHERE sub.CUSTOMER_ID = myTable.CUSTOMER_ID AND sub.PART_ID = myTable.PART_ID
    AND sub.EFFECTIVE_DATE > myTable.EFFECTIVE_DATE
  ORDER BY EFFECTIVE_DATE)