Check date split periods are continuous

Question

I have data in an Ingres table something like this;

REF     FROM_DATE   TO_DATE 
A       01.04.1997  01.04.1998
A       01.04.1998  27.05.1998
A       27.05.1998  01.04.1999

B       01.04.1997  01.04.1998
B       01.04.1998  26.07.1998
B       01.04.2012  01.04.2013

Some refs have continuous periods from the min(from_date) to the max(to_date), but some have gaps in the period.

I would like to know a way in Ingres SQL of identifying which refs have gaps in the date periods.

I am doing this as a Unix shell script calling the Ingres sql command.

Please advise.

Answer 1

I am not familiar with the date functions in Ingres. Let me assume that - gets the difference between two dates in days.

If there are no overlaps in the data, then you can do what you want pretty easily. If there are no gaps, then the difference between the minimum and maximum date is the same as the sum of the differences on each line. If the difference is greater than 0, then there are gaps.

So:

select ref,
       ((max(to_date) - min(from_date)) -
        sum(to_date - from_date)
       ) as total_gaps
from t
group by ref;

I believe this will work in your case. In other cases, there might be an "off-by-1" problem, depending on whether or not the end date is included in the period.