10 Replies Latest reply on May 3, 2013 1:40 PM by 650063

continious data range algorithm

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
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
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...

• 3. Re: continious data range algorithm
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
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
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
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.
• 8. Re: continious data range algorithm
CharlesRoos wrote:

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
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
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.