|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 ) );
Last updated 16 Aug 2019