Find sequenced duplicates

from the Artful Common Queries page


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 
  ) 
);

Return to the Artful Common Queries page