"holes" in a range of dates

[Anonymous]

Hi!

I have two fields that contains Dates, on field for "from dates" and one for "to dates". i'm trying to find holes in the date rage between from date and to date and from date and to date etc.

Hole > 1 date

For example, this is data for one item with several from dates and several to dates.

from date = 2015-02-03

to date = 2015-02-14

from date = 2015-02-15

to date = 2015-03-31

from date = 2015-04-04

to date = 2015-04-06

In the above example i have one hole between 2015-03-31 and 2015-04-04.

my question is, how do i find these holes?

script or not, every solution is welcome!

//A.

[Anonymous]

I did minor changed for it to work, thanks!

but.

When the from_date and the to_date is the same (Forgot to tell you it could) it markes it as Span >30.

[Anonymous]

thik i solved it... just added from_date <> to_date in the if statement.

[Anonymous]

I did found a case when this didn't work.

dates looking like this:

from_date          to_date

2015-01-07 2015-01-07

2015-01-08 2015-01-14

2015-01-15 2015-01-20

2015-01-20 2015-01-20

2015-01-21 2015-01-23

2015-01-24 2015-01-31

2015-02-01 2015-02-08

2015-02-09 2015-02-13

2015-02-14 2015-02-28

This is clearly a series of at least 30 days. But it doesn't mark it like that. I'm thinking maybe the tha fact that there's to FOM dates that are the same?

//A.

[rubenmarin]

Hi, in your last post you said "just added from_date <> to_date in the if statement."

Can you post your script to check and make some tests?

[Anonymous]

tmp:

LOAD

  from_date,

     to_date,

     ITEM,

     date,

     if(peek(to_date)>to_date, peek(to_date), to_date) as Max_to_date,

     if(from_date- peek(if(peek(to_date)>to_date, peek(to_date), to_date)) <= 1, Peek(from_date), from_date) as Min_from_date

Resident tmp

Order By ITEM, from_date;

Table:

LOAD

  from_date,

     to_date,

     ITEM,

     date,

     Max_to_date,

     Min_from_date,

     if(from_date- peek(Max_to_date) <= 1, if(to_date- peek(Min_from_date)>30, if(from_date<>to_date, '1'))) as [Span > 30],

     if(RowNo()=1 or from_date- peek(Max_to_date) > 1, 'Hole encountered') as HoleDetector

Resident tmp ;

drop table tmp;

[rubenmarin]

Hi, I removed ITEM and date (I have no data), you should put them again.

Problem was the Min_Date, please check attachment.

[Anonymous]

thanks! works great! //A