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

This query works in SQL SERVER. PARTITION is a ANSI SQL command, I don't know if INGRES supports it. if partition is supported probably you would have an equivalent to Dense_Rank()

select * 
INTO #TEMP
from (
select 'A' as Ref, Cast('1997-01-04' as DateTime) as From_date, Cast('1998-01-04' as DateTime) as to_date
union
select 'A' as Ref, Cast('1998-01-04' as DateTime) as From_date, Cast('1998-05-27' as DateTime) as to_date
union
select 'A' as Ref, Cast('1998-05-27' as DateTime) as From_date, Cast('1999-01-04' as DateTime) as to_date
union
select 'B' as Ref, Cast('1997-01-04' as DateTime) as From_date, Cast('1998-01-04' as DateTime) as to_date
union
select 'B' as Ref, Cast('1998-01-04' as DateTime) as From_date, Cast('1998-07-26' as DateTime) as to_date
union
select 'B' as Ref, Cast('2012-01-04' as DateTime) as From_date, Cast('2013-01-04' as DateTime) as to_date
) X

    SELECT *
    FROM
    (
          SELECT Ref, Min(NewStartDate) From_Date, MAX(To_Date) To_Date, COUNT(1) OVER (PARTITION BY Ref ) As [CountRanges]
          FROM
          (

                SELECT Ref, From_Date, To_Date,
                      NewStartDate = Range_UNTIL_NULL.From_Date + NUMBERS.number,
                      NewStartDateGroup =     DATEADD(d, 
                                                  1 - DENSE_RANK() OVER (PARTITION BY Ref ORDER BY Range_UNTIL_NULL.From_Date + NUMBERS.number), 
                                                  Range_UNTIL_NULL.From_Date + NUMBERS.number)
                FROM 
                (

                    --This subquery is necesary needed to "expand the To_date" to the next day and allowing it to be null 
                      SELECT 
                            REF, From_date, DATEADD(d, 1, ISNULL(To_Date, From_Date)) AS to_date
                      FROM #Temp T1
                      WHERE
                            NOT  EXISTS (   SELECT * 
                                            FROM #Temp t2 
                                            WHERE  T1.Ref = T2.Ref and T1.From_Date > T2.From_Date AND T2.To_Date IS NULL
                                        )
                )  AS Range_UNTIL_NULL
                CROSS APPLY  Enumerate ( ABS(DATEDIFF(d, From_Date, To_Date))) AS NUMBERS
                      ) X
          GROUP BY Ref, NewStartDateGroup
    ) OVERLAPED_RANGES_WITH_COUNT
--  WHERE OVERLAPED_RANGES_WITH_COUNT.CountRanges >= 2  --This filter is for identifying ranges that have at least one gap
    ORDER BY Ref, From_Date

The result for the given example is:

Ref  From_Date               To_Date                 CountRanges
---- ----------------------- ----------------------- -----------
A    1997-01-04 00:00:00.000 1999-01-05 00:00:00.000 1
B    1997-01-04 00:00:00.000 1998-07-27 00:00:00.000 2
B    2012-01-04 00:00:00.000 2013-01-05 00:00:00.000 2

as you can see those ref having "CountRanges" > 1 have at least one gap

This answer goes far beyound the initial question, because:

1. Ranges can be overlaped, is not clear if in the initial question that can happen
2. The question only ask which refs have gaps but with this query you can list the gaps
3. Tis query allows To_date in null, representing a semi segment to the infinite

Answer 2

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.