This discussion is archived
10 Replies Latest reply: May 3, 2013 6:40 AM by 650063 RSS

continious data range algorithm

650063 Journeyer
Currently Being Moderated
I have in my database table 2 important date columns: StartDate (Not null) and EndDate(Allowed Null).
I want to ensure that all records in the table would always create perfect contiues date ranges with no holes inside.
Wor example there may not be records [1-may..1-may, 3-may-...] because there would be a hole [2-may...2-may]. Holes are not allowed.
And overlapping is not allowed, for example [1-may..1-may, 1-may-2may, 3-may-...] is not allowed because overlapping occures on day 1-may. Overlapping and holes are not allowed. But it is allowed that table has no records at all. But all DML manipulations with existing records must ensure that overlapping and holes won't occur.

How to write such check? How to ensure that data ranges would stay continous with no holes and no overlaps?


--
Oracle 11g.
  • 1. Re: continious data range algorithm
    BluShadow Guru Moderator
    Currently Being Moderated
    How about providing some example data and expected output in a format we can use such as create table/insert statements... which you should already know to do. (see {message:id=9360002})
  • 2. Re: continious data range algorithm
    BluShadow Guru Moderator
    Currently Being Moderated
    You can use analytical functions to check for previous or next rows values to do comparisons, such as...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 1 as id, 1 as val1, 4 as val2 from dual union all
      2             select 1, 5, 6 from dual union all
      3             select 1, 7, null from dual union all
      4             select 2, 1, 3 from dual union all
      5             select 2, 4, 7 from dual union all
      6             select 2, 9, 12 from dual union all
      7             select 2, 13, null from dual union all
      8             select 3, 1, 3 from dual union all
      9             select 3, 4, null from dual
     10            )
     11  --
     12  select id
     13        ,val1 as "start"
     14        ,val2 as "end"
     15        ,case when nvl(lag(val2) over (partition by id order by val1),0) != val1-1 then 'hole or overlap' else null end as chk
     16  from t
     17* order by 1, 2
    SQL> /
    
            ID      start        end CHK
    ---------- ---------- ---------- ---------------
             1          1          4
             1          5          6
             1          7
             2          1          3
             2          4          7
             2          9         12 hole or overlap
             2         13
             3          1          3
             3          4
    
    9 rows selected.
    
    SQL>
    Or the tabibitosan method can be used if the data suits...

    {thread:id=1005478}
  • 3. Re: continious data range algorithm
    650063 Journeyer
    Currently Being Moderated
    that seems good solution.
    Example data with dates:
    with t as (    select 1 as id, to_date('01.04.2013', 'DD.MM.YYYY') as val1, to_date('04.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                   select 1, to_date('05.04.2013', 'DD.MM.YYYY'), to_date('06.04.2013', 'DD.MM.YYYY') from dual union all
                   select 1, to_date('07.04.2013', 'DD.MM.YYYY'), null from dual union all
                   select 2, to_date('01.04.2013', 'DD.MM.YYYY'), to_date('03.04.2013', 'DD.MM.YYYY') from dual union all
                   select 2, to_date('04.04.2013', 'DD.MM.YYYY'), to_date('07.04.2013', 'DD.MM.YYYY') from dual union all
                   select 2, to_date('09.04.2013', 'DD.MM.YYYY'), to_date('12.04.2013', 'DD.MM.YYYY') from dual union all
                   select 2, to_date('13.04.2013', 'DD.MM.YYYY'), null from dual union all
                   select 3, to_date('01.04.2013', 'DD.MM.YYYY'),to_date('03.04.2013', 'DD.MM.YYYY') from dual union all
                   select 3, to_date('04.04.2013', 'DD.MM.YYYY'), null from dual union all
                   select 4, to_date('01.04.2013', 'DD.MM.YYYY'), to_date('01.04.2013', 'DD.MM.YYYY') from dual union all
                   select 4, to_date('01.04.2013', 'DD.MM.YYYY'), null from dual
                 )
       --
       select id
             ,val1 as "start"
             ,val2 as "end",
             lag(val2) over (partition by id order by val1),
             val1-1
             ,case when 
                  nvl(lag(val2) over (partition by id order by val1),val1-1) != val1-1 then 
                        'hole or overlap' 
                   else null 
              end as chk
       from t
      order by 1, 2
    Seems wrong result when with dates:
    1     01-APR-13     04-APR-13          31-MAR-13     
    1     05-APR-13     06-APR-13     04-APR-13     04-APR-13     
    1     07-APR-13          06-APR-13     06-APR-13     
    2     01-APR-13     03-APR-13          31-MAR-13     
    2     04-APR-13     07-APR-13     03-APR-13     03-APR-13     
    2     09-APR-13     12-APR-13     07-APR-13     08-APR-13     hole or overlap
    2     13-APR-13          12-APR-13     12-APR-13     
    3     01-APR-13     03-APR-13          31-MAR-13     
    3     04-APR-13          03-APR-13     03-APR-13     
    4     01-APR-13     01-APR-13          31-MAR-13     
    4     01-APR-13          01-APR-13     31-MAR-13     hole or overlap
    Seems like works this way.

    Edited by: CharlesRoos on 3.05.2013 14:04
  • 4. Re: continious data range algorithm
    BluShadow Guru Moderator
    Currently Being Moderated
    You're setting the wrong value for the start of a group when there is no (null) lagging end date. In my example I set the value to 0 when it was null as I was expecting each group to start at 1. In your case you've set the date to 1/1/1900 which isn't necessarily the day before the first start date of the group. Instead just default it to the start date - 1 to force a match...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 1 as id, to_date('01.04.2013', 'DD.MM.YYYY') as val1, to_date('04.04.2013', 'DD.MM.YYYY') as val2 from dual union all
      2             select 1, to_date('05.04.2013', 'DD.MM.YYYY'), to_date('06.04.2013', 'DD.MM.YYYY') from dual union all
      3             select 1, to_date('07.04.2013', 'DD.MM.YYYY'), null from dual union all
      4             select 2, to_date('01.04.2013', 'DD.MM.YYYY'), to_date('03.04.2013', 'DD.MM.YYYY') from dual union all
      5             select 2, to_date('04.04.2013', 'DD.MM.YYYY'), to_date('07.04.2013', 'DD.MM.YYYY') from dual union all
      6             select 2, to_date('09.04.2013', 'DD.MM.YYYY'), to_date('12.04.2013', 'DD.MM.YYYY') from dual union all
      7             select 2, to_date('13.04.2013', 'DD.MM.YYYY'), null from dual union all
      8             select 3, to_date('01.04.2013', 'DD.MM.YYYY'),to_date('03.04.2013', 'DD.MM.YYYY') from dual union all
      9             select 3, to_date('04.04.2013', 'DD.MM.YYYY'), null from dual union all
     10             select 4, to_date('01.04.2013', 'DD.MM.YYYY'), to_date('01.04.2013', 'DD.MM.YYYY') from dual union all
     11             select 4, to_date('01.04.2013', 'DD.MM.YYYY'), null from dual
     12            )
     13  --
     14  select id
     15        ,val1 as "start"
     16        ,val2 as "end"
     17        ,lag(val2) over (partition by id order by val1)
     18        ,case when
     19             nvl(lag(val2) over (partition by id order by val1),val1-1) != val1-1 then
     20                   'hole or overlap'
     21              else null
     22         end as chk
     23  from t
     24* order by 1, 2
     25  /
    
            ID start                end                  LAG(VAL2)OVER(PARTIT CHK
    ---------- -------------------- -------------------- -------------------- ---------------
             1 01-APR-2013 00:00:00 04-APR-2013 00:00:00
             1 05-APR-2013 00:00:00 06-APR-2013 00:00:00 04-APR-2013 00:00:00
             1 07-APR-2013 00:00:00                      06-APR-2013 00:00:00
             2 01-APR-2013 00:00:00 03-APR-2013 00:00:00
             2 04-APR-2013 00:00:00 07-APR-2013 00:00:00 03-APR-2013 00:00:00
             2 09-APR-2013 00:00:00 12-APR-2013 00:00:00 07-APR-2013 00:00:00 hole or overlap
             2 13-APR-2013 00:00:00                      12-APR-2013 00:00:00
             3 01-APR-2013 00:00:00 03-APR-2013 00:00:00
             3 04-APR-2013 00:00:00                      03-APR-2013 00:00:00
             4 01-APR-2013 00:00:00 01-APR-2013 00:00:00
             4 01-APR-2013 00:00:00                      01-APR-2013 00:00:00 hole or overlap
    
    11 rows selected.
  • 5. Re: continious data range algorithm
    650063 Journeyer
    Currently Being Moderated
    Yes so, with "nvl(lag(val2) over (partition by id order by val1),val1-1".
    I came to solution same time myself too.
    Thx.
  • 6. Re: continious data range algorithm
    650063 Journeyer
    Currently Being Moderated
    But here sample test data gives wrong results:
    with t as (    select 1 as id, to_date('01.04.2013', 'DD.MM.YYYY') as val1, to_date('04.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                   select 4, to_date('02.05.2013', 'DD.MM.YYYY'), null from dual union all
                   select 4, to_date('09.05.2013', 'DD.MM.YYYY'), to_date('09.05.2013', 'DD.MM.YYYY') from dual
                 )
       --
       select id
             ,val1 as "start"
             ,val2 as "end",
             lag(val2) over (partition by id order by val1),
             val1-1
             ,case when 
                  nvl(lag(val2) over (partition by id order by val1),val1-1) != val1-1 then 
                        'hole or overlap' 
                   else null 
              end as chk
       from t
      order by 1, 2
    1     01-APR-13     04-APR-13          31-MAR-13     
    4     02-MAY-13               01-MAY-13     
    4     09-MAY-13     09-MAY-13          08-MAY-13     
    It should give for record id=4 phrase 'hole or overlap', because we have [02-MAY-13, ... ] that lasts till infinity, and 09-MAY-13 overlaps there it.
  • 7. Re: continious data range algorithm
    650063 Journeyer
    Currently Being Moderated
    Not answered.
  • 8. Re: continious data range algorithm
    BluShadow Guru Moderator
    Currently Being Moderated
    CharlesRoos wrote:
    Not answered.
    Not asked. :P

    The answers given are based on the scant information you have given.
    There are lots of ways of analysing previous and next records using analytical function, or using tabibitosan method, or even row generation techniques.
    However.... YOU haven't provided a concise example of the data that represents all the requirements, so you have been given answers based on guesses. If your requirements are different then it's up to YOU to provide sufficient information, otherwise you are just wasting people's time (mine in this case) in trying to guess what it is you want exactly.

    So... your call... provide a full example of the data you expect to be processing and the logic required, or just work on the information given, understand what it's doing, and adapt it to meet your new requirements.
  • 9. Re: continious data range algorithm
    Peter vd Zwan Expert
    Currently Being Moderated
    Hi,

    Maybe something like this:
    with t as (    select 1 as id, to_date('01.04.2013', 'DD.MM.YYYY') as val1, to_date('04.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                   select 2 as id, to_date('01.04.2013', 'DD.MM.YYYY') as val1, to_date('04.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                   select 2 as id, to_date('05.04.2013', 'DD.MM.YYYY') as val1, to_date('06.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                   select 2 as id, to_date('07.04.2013', 'DD.MM.YYYY') as val1, to_date('07.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                   select 2 as id, to_date('08.04.2013', 'DD.MM.YYYY') as val1, to_date('09.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                   select 3 as id, to_date('02.04.2013', 'DD.MM.YYYY') as val1, to_date('01.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                   select 4, to_date('02.05.2013', 'DD.MM.YYYY'), null from dual union all
                   select 4, to_date('09.05.2013', 'DD.MM.YYYY'), to_date('09.05.2013', 'DD.MM.YYYY') from dual
                 )
       --
       select id
             ,val1 as "start"
             ,val2 as "end"
             ,case when val1 is null then 'start date is null'
                   when val1 > val2 then 'end date before start date'
                   when row_number() over (partition by id order by val1) = 1 then null
                   when nvl(lag(val2) over (partition by id order by val1),val1-2) != val1-1 then 
                        'hole or overlap' 
                   else null 
              end as chk
       from t
      order by 1, 2
    ;
    
    ID start     end       CHK                      
    -- --------- --------- --------------------------
     1 01-APR-13 04-APR-13                            
     2 01-APR-13 04-APR-13                            
     2 05-APR-13 06-APR-13                            
     2 07-APR-13 07-APR-13                            
     2 08-APR-13 09-APR-13                            
     3 02-APR-13 01-APR-13 end date before start date 
     4 02-MAY-13                                      
     4 09-MAY-13 09-MAY-13 hole or overlap            
    
     8 rows selected 
    Regards,

    Peter
  • 10. Re: continious data range algorithm
    650063 Journeyer
    Currently Being Moderated
    Hi,
    Looks like the thing needed. I will test it through now.

    That validation is not needed : "end date before start date".
    We can assume that data is always as start_data <=end_date.

    Looks like perfect now.
    with t as (    select 1 as id, to_date('01.04.2013', 'DD.MM.YYYY') as val1, to_date('04.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                    select 1 as id, to_date('04.04.2013', 'DD.MM.YYYY') as val1, to_date('04.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                   select 3 as id, to_date('08.04.2013', 'DD.MM.YYYY') as val1, to_date('10.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                   select 3 as id, to_date('09.04.2013', 'DD.MM.YYYY') as val1, to_date('11.04.2013', 'DD.MM.YYYY') as val2 from dual union all
                   select 4, to_date('02.05.2013', 'DD.MM.YYYY'), null from dual union all
                   select 4, to_date('09.05.2013', 'DD.MM.YYYY'), to_date('09.05.2013', 'DD.MM.YYYY') from dual union all
                   select 5, to_date('02.05.2013', 'DD.MM.YYYY'), null from dual union all
                   select 5, to_date('01.05.2013', 'DD.MM.YYYY'), null from dual union all
                   select 6, to_date('5.05.2013', 'DD.MM.YYYY'), null from dual union all
                   select 7, to_date('5.05.2013', 'DD.MM.YYYY'), to_date('5.05.2013', 'DD.MM.YYYY') from dual
                 )
       --
       select id
             ,val1 as "start"
             ,val2 as "end"
             ,case when val1 is null then 'start date is null'
                   --when val1 > val2 then 'end date before start date'
                   when row_number() over (partition by id order by val1) = 1 then null
                   when nvl(lag(val2) over (partition by id order by val1),val1-2) != val1-1 then 
                        'hole or overlap' 
                   else null 
              end as chk
       from t
      order by 1, 2;
    1     01-APR-13     04-APR-13     
    1     04-APR-13     04-APR-13     hole or overlap
    3     08-APR-13     10-APR-13     
    3     09-APR-13     11-APR-13     hole or overlap
    4     02-MAY-13          
    4     09-MAY-13     09-MAY-13     hole or overlap
    5     01-MAY-13          
    5     02-MAY-13          hole or overlap
    6     05-MAY-13          
    7     05-MAY-13     05-MAY-13     
    This was the final solution, big thanks to you all.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points