1 2 3 Previous Next 35 Replies Latest reply on Dec 19, 2019 4:00 PM by Solomon Yakobson

    Check the consecutive dates(overlapping rows)

    Rosario Vigilante

      hello to all and thanks for help in advance

       

      Below are my details:

       

      --create table.
      create table t02(
      fname varchar2(10),
      city varchar2(10),
      country varchar2(5),
      start_date date,
      end_date date);
      /
      insert into t02 values('Tom','Boston','US','01-JAN-2001', '31-JUL-2001');
      insert into t02 values('Tom','Nashua','US','01-AUG-2001', '31-DEC-2001');
      insert into t02 values('Tom','New York','US','01-JAN-2002', '31-DEC-2002');
      insert into t02 values('Tom','Mumbai','IND','01-JAN-2003', '31-DEC-2003');
      insert into t02 values('Tom','Dallas','US','01-JAN-2004', '31-DEC-2004');
      insert into t02 values('Tom','Austin','US','01-JUL-2004', '31-DEC-2006');  -- How can I check it? IT would not possible insert a row where date is inside others, each start_date have to be > of previous end date of prev row
      /
      commit;
      
      /
      select * from t02
      order by start_date
      
      
      
      
      

       

       

      check must be in a BEFORE trigger

       

      Thanks

        • 2. Re: Check the consecutive dates(overlapping rows)
          mathguy

          There are several levels of complexity for constraints.

           

          The simplest constraints involve a single value at a time. A "value" is found in a single column of a single row. The NOT NULL constraint is perhaps the simplest example of such a constraint. Such constraints can be defined "inline" (right where you define the column in CREATE TABLE) or out-of-line (after all columns have been defined).

           

          More complex constraints reference values from at least two different columns, but all in the same row. These can only be written out-of-line.

           

          Even more complex constraints reference values from other ROWS in the same table. One example is the UNIQUE constraint - and, luckily, at least THAT type of constraint is handled for us by the database itself. (Similarly, referential integrity constraints may involve values from different TABLES, let alone from different ROWS in the same table; but they are handled for us by the database.)

           

          Alas, cross-row constraints - even in the same table - are notoriously difficult to implement, with the exception of UNIQUE which the database handles. There is no way to do it just with a BEFORE trigger, if you mean row-level trigger. (It can be done with a statement-level trigger, but that has serious performance consequences.)

           

          As far as I know, there are two main ways to enforce a constraint like what you are requesting. One is a compound trigger, the other is a materialized view approach. Both are quite a bit more advanced than a simple row-level trigger.

           

          So - which way do you plan to go? Or did you really mean a statement-level BEFORE trigger?

          1 person found this helpful
          • 3. Re: Check the consecutive dates(overlapping rows)
            mathguy

            Rosario Vigilante wrote:

             

            OK thanks I found

             

             

            https://oracle-base.com/articles/misc/overlapping-date-ranges

             

            What you found is fine and dandy - but you can't use that check in a TRIGGER - in a (row-level) trigger you can't select from the same table you are trying to modify. You will get the friendly "mutating table" error.

            1 person found this helpful
            • 4. Re: Check the consecutive dates(overlapping rows)
              Solomon Yakobson

              That's reactive approach. You can do more proactive approach using deferrable FK:

               

              create table t02(

                               fname varchar2(10),

                               city varchar2(10),

                               country varchar2(5),

                               start_date date,

                               end_date date

                              )

              /

              alter table t02

                add constraint t02_uk1

                  unique(start_date)

              /

              alter table t02

                add constraint t02_uk2

                  unique(end_date)

              /

              alter table t02

                add constraint t02_fk1

                  foreign key(start_date)

                  references t02(end_date) deferrable initially deferred

              /

              SQL> insert into t02 values('Tom','Boston','US','01-JAN-2001', '31-JUL-2001')

                2  /

               

              1 row created.

               

              SQL> insert into t02 values('Tom','New York','US','01-JAN-2002', '31-DEC-2002')

                2  /

               

              1 row created.

               

              SQL> insert into t02 values('Tom','Mumbai','IND','01-JAN-2003', '31-DEC-2003')

                2  /

               

              1 row created.

               

              SQL> insert into t02 values('Tom','Dallas','US','01-JAN-2004', '31-DEC-2004')

                2  /

               

              1 row created.

               

              SQL> insert into t02 values('Tom','Austin','US','01-JUL-2004', '31-DEC-2006')

                2  /

               

              1 row created.

               

              SQL> commit

                2  /

              commit

              *

              ERROR at line 1:

              ORA-02091: transaction rolled back

              ORA-02291: integrity constraint (SCOTT.T02_FK1) violated - parent key not found

               

              SQL>

               

              SY.

              1 person found this helpful
              • 6. Re: Check the consecutive dates(overlapping rows)
                Solomon Yakobson

                Actually you need to clarify "Check the consecutive dates(overlapping rows)". Based on sample provided you need to check for overlapping but not for consecutive dates. Solution I provided ensures dates are consecutive, therefore no gaps and no overlaps are allowed (at commit point).

                 

                SY.

                1 person found this helpful
                • 7. Re: Check the consecutive dates(overlapping rows)
                  Stew Ashton

                  It looks like there has to be a starting point with a NULL start date. In fact, why can't there be more than one?

                   

                  SQL> truncate table T02;
                  
                  Table T02 truncated.
                  
                  SQL> insert into t02(start_date,end_date) values(null, date '2000-01-01');
                  
                  1 row inserted.
                  
                  SQL> commit;
                  
                  Commit complete.
                  
                  SQL> insert into T02(start_date,end_date) values(null, date '2000-02-02');
                  
                  1 row inserted.
                  
                  SQL> commit;
                  
                  Commit complete.
                  
                  SQL> insert into T02(start_date,end_date) values(date '2000-01-01', date '2000-03-03');
                  
                  1 row inserted.
                  
                  SQL> commit;
                  
                  Commit complete.
                  
                  SQL> insert into T02(start_date,end_date) values(date '2000-02-02', date '2000-04-04');
                  
                  1 row inserted.
                  
                  SQL> commit;
                  
                  Commit complete.
                  

                   

                  Don't we need an additional constraint that makes the NULL start date unique?

                   

                  Also I suppose you are assuming the OP will think of the CHECK constraint that makes END_DATE later than START_DATE?

                   

                  QL> truncate table T02;
                  
                  Table T02 truncated.
                  
                  SQL> insert into t02(start_date,end_date) values(null, date '2000-01-01');
                  
                  1 row inserted.
                  
                  SQL> insert into t02(start_date,end_date) values(date '2000-01-01', date '2001-01-01');
                  
                  1 row inserted.
                  
                  SQL> insert into t02(start_date,end_date) values(date '2001-01-01', date '2000-06-01');
                  
                  1 row inserted.
                  
                  SQL> commit;
                  
                  Commit complete.
                  

                   

                  Best regards,

                  Stew Ashton

                  1 person found this helpful
                  • 8. Re: Check the consecutive dates(overlapping rows)
                    Solomon Yakobson

                    Good point. We can add:

                     

                    create unique index to2_uidx1

                      on t02(

                            nvl(start_date,date '-4712-12-31')

                            )

                    /

                     

                    SY.

                    1 person found this helpful
                    • 9. Re: Check the consecutive dates(overlapping rows)
                      Solomon Yakobson

                      Stew Ashton wrote:

                       

                      It looks like there has to be a starting point with a NULL start date.

                      Not necessarily. We do need starting point. It can be either null start date or row with start date = end date. For a more generic solution without a need for null start date or a start row we can use fast refresh on commit materialized view.

                       

                      SY.

                      • 10. Re: Check the consecutive dates(overlapping rows)
                        Solomon Yakobson

                        Trigger + end map table solution:

                         

                        create table t02(

                                         fname varchar2(10),

                                         city varchar2(10),

                                         country varchar2(5),

                                         start_date date not null,

                                         end_date date not null

                                        )

                        /

                        alter table t02

                          add constraint t02_chk1

                            check(end_date >= start_date)

                        /

                        create table t02_end_map

                          as

                            select  cast(null as date) first_start_date,

                                    cast(null as date) last_end_date

                              from  dual

                        /

                        create or replace

                          trigger t02_biudr

                            before insert

                                or update

                                or delete

                            on t02

                            for each row

                            declare

                                v_first_start_date date;

                                v_last_end_date   date;

                            begin

                                select  first_start_date,

                                        last_end_date

                                  into  v_first_start_date,

                                        v_last_end_date

                                  from  t02_end_map

                                    for update;

                                if inserting

                                  then

                                    if :new.start_date = nvl(v_last_end_date + 1,:new.start_date)

                                       then

                                         update t02_end_map

                                            set first_start_date = nvl(first_start_date,:new.start_date),

                                                last_end_date    = :new.end_date;

                                    elsif :new.end_date = nvl(v_first_start_date - 1,:new.end_date)

                                       then

                                         update t02_end_map

                                            set first_start_date = :new.start_date,

                                                last_end_date    = nvl(last_end_date,:new.end_date);

                                    else

                                      raise_application_error(

                                                              -20500,

                                                              'Gap/overlap detected.'

                                                             );

                                    end if;

                                elsif updating

                                  then

                                    if :old.end_date = v_last_end_date and :old.start_date = :new.start_date

                                       then

                                         update t02_end_map

                                            set last_end_date    = :new.end_date;

                                    elsif :old.start_date = v_first_start_date and :old.end_date = :new.end_date

                                       then

                                         update t02_end_map

                                            set first_start_date = :new.start_date;

                                    elsif :old.end_date = v_last_end_date and :new.end_date = v_first_start_date - 1

                                       then

                                         update t02_end_map

                                            set first_start_date = :new.start_date,

                                                last_end_date    = :old.start_date - 1;

                                    elsif :old.start_date = v_first_start_date and :new.start_date = v_last_end_date + 1

                                       then

                                         update t02_end_map

                                            set first_start_date = :old.end_date + 1,

                                                last_end_date    = :new.end_date;

                                    else

                                      raise_application_error(

                                                              -20500,

                                                              'Gap/overlap detected.'

                                                             );

                                    end if;

                                  else

                                    if :old.end_date = v_last_end_date

                                       then

                                         update t02_end_map

                                            set last_end_date = case

                                                                  when v_first_start_date <= :old.start_date - 1 then :old.start_date - 1

                                                                end;

                                    elsif :old.start_date = v_first_start_date

                                       then

                                         update t02_end_map

                                            set first_start_date = case

                                                                     when v_last_end_date >= :old.end_date + 1 then :old.end_date + 1

                                                                   end;

                                    else

                                      raise_application_error(

                                                              -20500,

                                                              'Gap/overlap detected.'

                                                             );

                                    end if;

                                end if;

                        end;

                        /

                         

                        Now:

                         

                        SQL> insert into t02 values('Tom','Boston','US','01-JAN-2001', '31-JUL-2001')

                          2  /

                         

                        1 row created.

                         

                        SQL> insert into t02 values('Tom','New York','US','01-JAN-2002', '31-DEC-2002')

                          2  /

                        insert into t02 values('Tom','New York','US','01-JAN-2002', '31-DEC-2002')

                        *

                        ERROR at line 1:

                        ORA-20500: Gap/overlap detected.

                        ORA-06512: at "SCOTT.T02_BIUDR", line 22

                        ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

                         

                        SQL> insert into t02 values('Tom','New York','US','01-AUG-2001', '31-DEC-2002')

                          2  /

                         

                        1 row created.

                         

                        SQL> insert into t02 values('Tom','Mumbai','IND','01-OCT-2001', '01-NOV-2002')

                          2  /

                        insert into t02 values('Tom','Mumbai','IND','01-OCT-2001', '01-NOV-2002')

                                    *

                        ERROR at line 1:

                        ORA-20500: Gap/overlap detected.

                        ORA-06512: at "SCOTT.T02_BIUDR", line 22

                        ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

                         

                        SQL> insert into t02 values('Tom','Mumbai','IND','01-OCT-2000', '01-NOV-2000')

                          2  /

                        insert into t02 values('Tom','Mumbai','IND','01-OCT-2000', '01-NOV-2000')

                                    *

                        ERROR at line 1:

                        ORA-20500: Gap/overlap detected.

                        ORA-06512: at "SCOTT.T02_BIUDR", line 22

                        ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

                         

                        SQL> insert into t02 values('Tom','Mumbai','IND','01-OCT-2000', '31-DEC-2000')

                          2  /

                         

                        1 row created.

                         

                        SQL> insert into t02 values('Tom','Dallas','US','01-jan-03','31-dec-03')

                          2  /

                         

                        1 row created.

                         

                        SQL> select * from t02 order by start_date;

                         

                        FNAME      CITY       COUNT START_DAT END_DATE

                        ---------- ---------- ----- --------- ---------

                        Tom        Mumbai     IND   01-OCT-00 31-DEC-00

                        Tom        Boston     US    01-JAN-01 31-JUL-01

                        Tom        New York   US    01-AUG-01 31-DEC-02

                         

                        SQL> update t02

                          2     set end_date = date '2002-12-01'

                          3   where city = 'New York'

                          4  /

                         

                        1 row updated.

                         

                        SQL> select * from t02 order by start_date;

                         

                        FNAME      CITY       COUNT START_DAT END_DATE

                        ---------- ---------- ----- --------- ---------

                        Tom        Mumbai     IND   01-OCT-00 31-DEC-00

                        Tom        Boston     US    01-JAN-01 31-JUL-01

                        Tom        New York   US    01-AUG-01 01-DEC-02

                         

                        SQL> update t02

                          2     set end_date = date '2003-12-01'

                          3   where city = 'New York'

                          4  /

                         

                        1 row updated.

                         

                        SQL> select * from t02 order by start_date;

                         

                        FNAME      CITY       COUNT START_DAT END_DATE

                        ---------- ---------- ----- --------- ---------

                        Tom        Mumbai     IND   01-OCT-00 31-DEC-00

                        Tom        Boston     US    01-JAN-01 31-JUL-01

                        Tom        New York   US    01-AUG-01 01-DEC-03

                         

                        SQL> select * from t02 order by start_date;

                         

                        FNAME      CITY       COUNT START_DAT END_DATE

                        ---------- ---------- ----- --------- ---------

                        Tom        Mumbai     IND   01-OCT-00 31-DEC-00

                        Tom        Boston     US    01-JAN-01 31-JUL-01

                        Tom        New York   US    01-AUG-01 01-DEC-02

                         

                        SQL> update t02

                          2     set end_date = date '2003-12-01'

                          3   where city = 'New York'

                          4  /

                         

                        1 row updated.

                         

                        SQL> select * from t02 order by start_date;

                         

                        FNAME      CITY       COUNT START_DAT END_DATE

                        ---------- ---------- ----- --------- ---------

                        Tom        Mumbai     IND   01-OCT-00 31-DEC-00

                        Tom        Boston     US    01-JAN-01 31-JUL-01

                        Tom        New York   US    01-AUG-01 01-DEC-03

                         

                        SQL> update t02

                          2     set start_date = date '2000-11-01'

                          3  /

                        update t02

                               *

                        ERROR at line 1:

                        ORA-20500: Gap/overlap detected.

                        ORA-06512: at "SCOTT.T02_BIUDR", line 40

                        ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

                         

                        SQL> update t02

                          2     set start_date = date '2000-07-01'

                          3  /

                        update t02

                               *

                        ERROR at line 1:

                        ORA-20500: Gap/overlap detected.

                        ORA-06512: at "SCOTT.T02_BIUDR", line 40

                        ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

                         

                        SQL> update t02

                          2     set start_date = date '2000-11-01'

                          3   where city = 'Mumbai'

                          4  /

                         

                        1 row updated.

                         

                        SQL> select * from t02 order by start_date;

                         

                        FNAME      CITY       COUNT START_DAT END_DATE

                        ---------- ---------- ----- --------- ---------

                        Tom        Mumbai     IND   01-NOV-00 31-DEC-00

                        Tom        Boston     US    01-JAN-01 31-JUL-01

                        Tom        New York   US    01-AUG-01 01-DEC-03

                         

                        SQL> update t02

                          2     set start_date = date '2000-07-01'

                          3   where city = 'Mumbai'

                          4  /

                         

                        1 row updated.

                         

                        SQL> update t02

                          2     set start_date = date '2001-01-05'

                          3   where city = 'Boston'

                          4  /

                        update t02

                               *

                        ERROR at line 1:

                        ORA-20500: Gap/overlap detected.

                        ORA-06512: at "SCOTT.T02_BIUDR", line 40

                        ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

                         

                        SQL> update t02

                          2     set start_date = date '2000-11-01'

                          3   where city = 'Boston'

                          4  /

                        update t02

                               *

                        ERROR at line 1:

                        ORA-20500: Gap/overlap detected.

                        ORA-06512: at "SCOTT.T02_BIUDR", line 40

                        ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

                         

                        SQL> update t02

                          2     set end_date = date '2001-03-01'

                          3   where city = 'Boston'

                          4  /

                        update t02

                               *

                        ERROR at line 1:

                        ORA-20500: Gap/overlap detected.

                        ORA-06512: at "SCOTT.T02_BIUDR", line 40

                        ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

                         

                        SQL> update t02

                          2     set end_date = date '2002-03-01'

                          3   where city = 'Boston'

                          4  /

                        update t02

                               *

                        ERROR at line 1:

                        ORA-20500: Gap/overlap detected.

                        ORA-06512: at "SCOTT.T02_BIUDR", line 40

                        ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

                         

                        SQL> delete t02

                          2   where city = 'Boston'

                          3  /

                        delete t02

                               *

                        ERROR at line 1:

                        ORA-20500: Gap/overlap detected.

                        ORA-06512: at "SCOTT.T02_BIUDR", line 59

                        ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

                         

                        SQL> delete t02

                          2   where city = 'New York'

                          3  /

                         

                        1 row deleted.

                         

                        SQL> delete t02

                          2   where city = 'Boston'

                          3  /

                         

                        1 row deleted.

                         

                        SQL> delete t02

                          2   where city = 'Mumbai'

                          3  /

                         

                        1 row deleted.

                         

                        SQL> select * from t02_end_map;

                         

                        FIRST_STA LAST_END_

                        --------- ---------

                         

                        SQL>

                         

                        This is pessimistic solution - it serializes the process comparing to optimistic FAST REFRESH ON COMMIT or deferred FK solution which assumes transactions make valid inserts.

                         

                        SY.

                        P.S. Corrected IF UPDATING.

                        • 11. Re: Check the consecutive dates(overlapping rows)
                          Stew Ashton

                          Solomon Yakobson wrote:

                           

                          Stew Ashton wrote:

                           

                          It looks like there has to be a starting point with a NULL start date.

                          Not necessarily. We do need starting point. It can be either null start date or row with start date = end date. For a more generic solution without a need for null start date or a start row we can use fast refresh on commit materialized view.

                           

                          SY.

                          You missed my point. To avoid overlaps, we need a UNIQUE starting point. With the solution you first proposed - don't change the goal posts yet - there can be multiple starting points with null start dates or multiple starting points with start data = end date.

                           

                          [UPDATE 2019-12-15: come to think of it, a row with start date = end date cannot be a "starting point"! Suppose we insert a row with start_date = end_date = 2000-01-01. There are unique constraints on start_date and end_date; that means 2000-01-01 cannot be used again in any row, either in start_date or end_date. With Solomon's solution, a real "starting point" has to have a NULL start date. With my solution below, the "starting point" has to have a NULL end date.]

                           

                          Building on your first solution, I would be tempted to do this:

                          • Make the end date an exclusive end date as you do. The OP uses inclusive end dates.
                          • The start date cannot be null, but the end date can be null to indicate the indefinite future. This way we don't need a fake row to start things off.
                          • Check constraint: start date must be less than end date.
                          • Unique constraints on start data and end date.
                          • A deferred constraint where the end date references some other start date (like your constraint but going the other way).
                          • A virtual column that is 'Y' when the end date is null, plus a unique constraint on that column.

                          create table t02(
                            fname varchar2(10),
                            city varchar2(10),
                            country varchar2(5),
                            start_date date not null
                              constraint t02_start_date_uk unique,
                            end_date date
                              constraint t02_end_date_uk unique,
                            is_ending_point invisible
                              generated always as (case when end_date is null then 'Y' end)
                              constraint t02_one_ending_point_only unique,
                            constraint t02_start_lt_end_ck check (start_date < end_date),
                            constraint t02_end_date_eq_other_start
                              foreign key(end_date)
                              references t02(start_date) deferrable initially deferred
                          );
                          

                           

                          Another way of getting the same result with much less trouble: just get rid of the end date!

                           

                          create table t02(
                            fname varchar2(10),
                            city varchar2(10),
                            country varchar2(5),
                            effective_date date not null
                              constraint t02_effective_date_uk unique
                          );
                          
                          create or replace view v_t02 as
                          select fname, city, country, 
                            effective_date start_date,
                            (select min(effective_date)
                             from t02
                             where effective_date > a.effective_date) end_date
                          from t02 a;
                          

                           

                          Either way there must be a known first start date, there can be no gaps or overlaps or known final end date.

                           

                          Regards,

                          Stew

                          1 person found this helpful
                          • 12. Re: Check the consecutive dates(overlapping rows)
                            Priyasagi

                            select * from (

                                 select     fname,city,country,start_date,

                                               lag(start_date,1) over (order by start_date) pre_start,

                                               lag(end_date,1) over (order by end_date) pre_end  from t02)

                                 where start_date between pre_start and pre_end;

                             

                             

                            output

                               

                            FNAMECITYCOUNTRYSTART_DATEPRE_STARTPRE_END
                            TomAustinUS01-07-200401-01-200431-12-2004
                            1 person found this helpful
                            • 13. Re: Check the consecutive dates(overlapping rows)
                              Solomon Yakobson

                              Stew Ashton wrote:

                               

                              Building on your first solution, I would be tempted to do this:

                              • Make the end date an exclusive end date as you do. The OP uses inclusive end dates.
                              • The start date cannot be null, but the end date can be null to indicate the indefinite future. This way we don't need a fake row to start things off.
                              • Check constraint: start date must be less than end date.
                              • Unique constraints on start data and end date.
                              • A deferred constraint where the end date references some other start date (like your constraint but going the other way).
                              • A virtual column that is 'Y' when the end date is null, plus a unique constraint on that column.

                              Inclusive dates can be handled by a virtual column. Not sure why start date can't be null to indicate indefinite past. Check constraint is, indeed, needed (you can see it in my trigger solution) but it should be start date must be less or equal than end date. In regards to "This way we don't need a fake row to start things off". That will work for indefinite future intervals only. How would you insert "Boston", for example:

                               

                              SQL> create table t22(

                                2    fname varchar2(10),

                                3    city varchar2(10),

                                4    country varchar2(5),

                                5    start_date date not null

                                6      constraint t22_start_date_uk unique,

                                7    end_date date

                                8      constraint t22_end_date_uk unique,

                                9    is_ending_point invisible

                              10      generated always as (case when end_date is null then 'Y' end)

                              11      constraint t22_one_ending_point_only unique,

                              12    constraint t22_start_lt_end_ck check (start_date < end_date),

                              13    constraint t22_end_date_eq_other_start

                              14      foreign key(end_date)

                              15      references t22(start_date) deferrable initially deferred

                              16  )

                              17  /

                               

                              Table created.

                               

                              SQL> insert into t22 values('Tom','Boston','US',date '2001-01-01',date '2001-07-31')

                                2  /

                               

                              1 row created.

                               

                              SQL> commit

                                2  /

                              commit

                              *

                              ERROR at line 1:

                              ORA-02091: transaction rolled back

                              ORA-02291: integrity constraint (SCOTT.T22_END_DATE_EQ_OTHER_START) violated - parent key not found

                               

                              SQL> insert into t22 values('Tom','Boston','US',date '2001-01-01',null)

                                2  /

                               

                              1 row created.

                               

                              SQL> update t22 set end_date = date '2001-07-31' where city = 'Boston'

                                2  /

                               

                              1 row updated.

                               

                              SQL> commit

                                2  /

                              commit

                              *

                              ERROR at line 1:

                              ORA-02091: transaction rolled back

                              ORA-02291: integrity constraint (SCOTT.T22_END_DATE_EQ_OTHER_START) violated - parent key not found

                               

                              SQL>

                               

                              You would have to keep fake indefinite end date row:

                               

                              SQL> insert into t22 values('Tom','Boston','US',date '2001-01-01',date '2001-07-31')

                                2  /

                               

                              1 row created.

                               

                              SQL> insert into t22 values('Tom','Boston','US',date '2001-07-31',null)

                                2  /

                               

                              1 row created.

                               

                              SQL> commit

                                2  /

                               

                              Commit complete.

                               

                              SQL>

                               

                              SY.

                              1 person found this helpful
                              • 14. Re: Check the consecutive dates(overlapping rows)
                                Stew Ashton

                                Solomon Yakobson wrote:

                                ...

                                Inclusive dates can be handled by a virtual column.

                                True. I was merely pointing out that in your first reply you used exclusive dates (which I recommend).

                                 

                                Not sure why start date can't be null to indicate indefinite past.

                                It can, but I chose to switch to null end date to indicate indefinite future. Can we have both indefinite? I suppose, but at the cost of more constraints.

                                 

                                Check constraint is, indeed, needed (you can see it in my trigger solution) but it should be start date must be less or equal than end date.

                                I disagree.

                                 

                                In regards to "This way we don't need a fake row to start things off". That will work for indefinite future intervals only.

                                Correct. As I stated, "Either way there must be a known first start date, there can be no gaps or overlaps or known final end date."

                                 

                                Regards,

                                Stew

                                1 person found this helpful
                                1 2 3 Previous Next