Oracle SQL: Finding Overlapping Dates

When the object validation error reported,
"An account cannot have multiple auto-pay options effective at the same time"

To find out the overlapping dates, following query became handy. This can be generalized for any situation where overlapping periods of data needs to be identified.


SELECT *
FROM   (SELECT acct_id,
               start_dt,
               end_dt,
               CASE
                 WHEN NOT (start_dt > Lead(end_dt)
                                        OVER(PARTITION BY acct_id ORDER BY start_dt, end_dt)
                            OR end_dt < Lead(start_dt)
                                          OVER(PARTITION BY acct_id ORDER BY start_dt, end_dt))
                 THEN 'Yes'
                 ELSE 'No'
               END AS overlap
        FROM   ci_acct_apay)
WHERE  overlap = 'Yes'