Find sequenced duplicates

A table that tracks time periods may require period uniqueness. That means it has no sequenced duplicates.

If a table has columns processID, start_date and end_date, those three columns are period unique if there exists no pair of rows with the same processID and overlapping start_date and end_date values. If there is such a pair of rows, the table exhibits sequenced duplication.

Another way of saying it: if an instant is the smallest datetime unit of start_date and end_date columns, then if there are no sequenced duplicates, there is exactly one processID value at any instant.

Here is a query to find sequenced duplicates for those columns:



SELECT t.processid 

FROM tbl t 

WHERE EXISTS ( 

  SELECT * FROM tbl AS t3 

  WHERE t3.processid IS NULL 

) 

OR EXISTS ( 

  SELECT * FROM tbl AS t1 

  WHERE 1 < ( 

    SELECT COUNT(processid) 

    FROM tbl AS t2 

    WHERE t1.processid = t2.processid 

      AND t1.start_date < t2.end_date 

      AND t2.start_date < t1.end_date 

  ) 

);