16 Replies Latest reply: Mar 26, 2015 9:43 AM by Annika Palm RSS

    "holes" in a range of dates

    Annika Palm

      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.

        • Re: "holes" in a range of dates
          Gysbert Wassenaar

          Assuming your data is already sorted by from_date and you don't have overlapping intervals:

           

          LOAD

               from_date,

               to_date,

               if(from_date - previous(to_date) > 1, 'Hole encountered')

          FROM ...

          • Re: "holes" in a range of dates
            Ruben Marin

            Hi, you can do momething like Data2 table in this script:

            Data:

            LOAD Date(Date#(from_date, 'YYYY-MM-DD'), 'DD/MM/YYYY') as from_date,

                Date(Date#(to_date, 'YYYY-MM-DD'), 'DD/MM/YYYY') as to_date;

            LOAD * Inline [

            from_date, to_date

            2015-02-03, 2015-02-14

            2015-02-15, 2015-03-31

            2015-04-04, 2015-04-06

            ];

             

            Data2:

            NoConcatenate

            LOAD from_date, to_date,

                from_date-Peek(to_date) as Daysdiff

            Resident Data Order By from_date;

             

            DROP Table Data;

             

            Then wich DaysDiff>1, those are the dates that starts more than one day after the last to_date.

            • Re: "holes" in a range of dates
              Annika Palm

              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.
              • Re: "holes" in a range of dates
                Annika Palm

                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;