Overlapping Locator approach

I have been writing earlier about handling overlapping time periods. Those approaches are trying to deal populating rows without mutating table problems and compound triggers. In this post there is a query example dealing with a solution possibility while dealing with such data. But first a list of earlier posts about the issue:

• not overlapping
• MV approach
• exclusion constraints
• Packing rows
• joining ranges
• sum over time
• not overlapping daily

Having a requirement that periods are not overlapping and the timeline has to be continuous. It is possible to use SDO_AGGR_CONCAT_LINES aggregate function to find out violations of such rule.

drop table z;

CREATE TABLE Z(V VARCHAR2(10) NOT NULL
             , VALIDFROM date NOT NULL
             , VALIDTILL date NOT NULL
             , CONSTRAINT FRO2000 CHECK (to_date('20000101','yyyymmdd') < VALIDFROM)
             , CONSTRAINT TIL2050 CHECK (VALIDTILL <= to_date('20510101','yyyymmdd'))
             , CONSTRAINT FROTIL CHECK (VALIDFROM <= VALIDTILL)
             );

INSERT INTO Z VALUES('meet',to_date('20010101','yyyymmdd'),to_date('20110101','yyyymmdd'));

INSERT INTO Z VALUES('meet',to_date('20110101','yyyymmdd'),to_date('20130101','yyyymmdd'));

INSERT INTO Z VALUES('overlap',to_date('20010101','yyyymmdd'),to_date('20110101','yyyymmdd'));

INSERT INTO Z VALUES('overlap',to_date('20050101','yyyymmdd'),to_date('20130101','yyyymmdd'));

INSERT INTO Z VALUES('disjoint',to_date('20010101','yyyymmdd'),to_date('20050101','yyyymmdd'));

INSERT INTO Z VALUES('disjoint',to_date('20110101','yyyymmdd'),to_date('20130101','yyyymmdd'));

COMMIT;

Creating a function that constructs SDO_GEOMETRY object out of periods.

CREATE OR REPLACE FUNCTION TF(FRO DATE, TIL DATE) RETURN SDO_GEOMETRY deterministic as 
BEGIN
RETURN MDSYS.SDO_GEOMETRY(2002,NULL, NULL, SDO_ELEM_INFO_ARRAY (1,2,1),
    SDO_ORDINATE_ARRAY(to_number(to_char(FRO,'j')),0,to_number(to_char(TIL,'j')),0));
END;
/

A query that groups the periods of different v values. When the aggregated SDO_GEOMETRY object is a line SDO_GTYPE=2002 the requirement is fine. Otherwise it is a multiline SDO_GTYPE=2006.

select v
     , min(validfrom) mi
     , max(validtill) ma
     , SDO_AGGR_CONCAT_LINES(tf(validfrom,validtill)).SDO_GTYPE gt
  from z
 group by v
;

v           mi          ma          gt
disjoint    1.1.2001    1.1.2013    2006
meet        1.1.2001    1.1.2013    2002
overlap     1.1.2001    1.1.2013    2006

The violations of the rule are multilines. The aggregate function may be used in having part of a query.

select v
     , min(validfrom)
     , max(validtill)
     , SDO_AGGR_CONCAT_LINES(tf(validfrom,validtill)).SDO_GTYPE gt
  from z
 group by v
 having SDO_AGGR_CONCAT_LINES(tf(validfrom,validtill)).SDO_GTYPE = 2006
;