10 Replies Latest reply: May 3, 2013 8:40 AM by 650063 RSS

    continious data range algorithm

    650063
      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
          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
            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
              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
                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
                  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
                    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
                      Not answered.
                      • 8. Re: continious data range algorithm
                        BluShadow
                        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
                          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
                            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.