9 Replies Latest reply on Feb 11, 2014 12:41 PM by Stew Ashton

    Check crossing time periods between rows

    Tobias Arnhold

      Hi,

       

      I have the problem creating a select to check between crossing time periods.

       

      This is my table

      CREATE TABLE "T_PLAN"

      (  "TRAIN_NO" NUMBER,

          "TIME_FROM" VARCHAR2(5),

          "TIME_UNTIL" VARCHAR2(5),

          "RAIL_NO" NUMBER

      ) ;

       

      TRAIN_NO    TIME_FROM    TIME_UNTIL    RAIL_NO

      172    07:06    15:50    1

      120    07:33    15:41    2

      229    07:56    14:52    3

      217    15:05    18:47    1

      189    16:10    19:53    2

      185    16:10    19:53    3

      181    20:10    23:53    2

      142    29:14    23:32    3

       

      I want to check if my train with number 172 is crossing another row with the same RAIL_NO.

      In that case train number 217 is hitting the same time period as 172 does. Because: 15:05 <= 15:50

       

      The output I need:

      TRAIN_NO    TIME_FROM    TIME_UNTIL    RAIL_NO CHECK_ROW

      172    07:06    15:50    1     UNVALID

      120    07:33    15:41    2     VALID

      229    07:56    14:52    3     VALID

      217    15:05    18:47    1     UNVALID

      189    16:10    19:53    2     VALID

      185    16:10    19:53    3     VALID

      181    20:10    23:53    2     VALID

      142    20:14    23:32    3     VALID

       

      Thanks for your support.

       

      Cheers Tobias

        • 1. Re: Check crossing time periods between rows
          Frank Kulash

          Hi,

           

          Here's one way:

          SELECT DISTINCT

                    d.*

          ,         NVL2 ( o.rail_no

                         , 'INVALID'  -- or 'UNVALID', if you really want that

                         , 'VALID'

                         )    AS check_row

          FROM             t_plan  d

          LEFT OUTER JOIN  t_plan  o  ON   o.rail_no    =  d.rail_no

                                      AND  o.time_from  <= d.time_until

                                      AND  o.time_until >= d.time_from

          ORDER BY  d.time_from;

          If you'd care to post INSERT statements for your sample data, then I could test this.

           

          Two ranges overlap if (and only if) each one begins before the other one ends.  This will be more clear if you consider that two ranges do not overlap if (and only if) one of them begins after the other one ends.

           

          This solution assumes that time_from <= time_until on every row.  Is that true?  For example, if a rail is in use at midnight, could you have (for example) time_from='23:55' and time_intil='00:05' on the same row?

          • 2. Re: Check crossing time periods between rows
            RogerT

            and another way...

             

            WITH d AS (SELECT 172 train, to_date('07:06','hh24:mi') AS sd, to_date('15:50','hh24:mi') AS ed, 1 rail FROM dual UNION ALL

                       select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all

                       select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all

                       SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all

                       select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all

                       SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all

                       SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL

                       SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual)

            SELECT train, rail, sd, ed, prev_ed, next_sd

                 , CASE WHEN (    (prev_ed IS NULL OR sd > prev_ed)

                              and (next_sd is null or ed < next_sd))

                        THEN 'VALID'

                        ELSE 'INVALID'

                   end as validity

              from (SELECT train, rail

                        , to_number(to_char(sd,'hh24mi')) AS sd

                        , to_number(to_char(ed,'hh24mi')) AS ed

                        , to_number(to_char(lag(ed) OVER (PARTITION BY rail ORDER BY sd),'hh24mi')) prev_ed

                        , to_number(to_char(lead(sd) OVER (PARTITION BY rail ORDER BY sd),'hh24mi')) next_sd

                       from d)          

            1 person found this helpful
            • 3. Re: Check crossing time periods between rows
              Frank Kulash

              Hi,

               

              LEAD and LAG can't do this job.  LEAD and LAG can only compare a row to 1 neighboring row.  In this problem (if I understand it correctly) we need to compare each row to all rows with the same rail_no, not just the neighboring rows.  For example, if, on the same rail_no we had ranges like this:

               

              01:00   02:00

              01:05   01:10

              01:15   01:20

              01:25   01:30

               

              we would want to say that they were all invalid, since the first row (01:00-02:00) overlaps each of the others.

              1 person found this helpful
              • 5. Re: Check crossing time periods between rows
                Nimish Garg

                Try this

                 

                WITH t AS 
                (
                  SELECT 172 TRAIN_NO, to_date('07:06','hh24:mi') AS TIME_FROM, to_date('15:50','hh24:mi') AS TIME_UNTIL    , 1 RAIL_NO FROM dual UNION ALL
                  select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all
                  select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all
                  SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all
                  select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all
                  SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all
                  SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL
                  SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual
                )
                select
                  t.TRAIN_NO,
                  t.TIME_FROM,
                  t.TIME_UNTIL,
                  t.RAIL_NO,
                  decode(t2.TRAIN_NO,NULL,'VALID','INVALID') CHECK_ROW
                from
                  t,
                  (
                    select
                      t1.TRAIN_NO
                    from
                      t t1, t t2
                    where
                      t1.RAIL_NO = t2.RAIL_NO
                      and t1.TIME_FROM <= t2.TIME_UNTIL
                      and t1.TIME_UNTIL >= t2.TIME_FROM
                      and t1.TRAIN_NO <> t2.TRAIN_NO
                  ) t2
                  where t.TRAIN_NO = t2.TRAIN_NO (+)
                  order by TIME_FROM
                
                1 person found this helpful
                • 6. Re: Check crossing time periods between rows
                  Partha Sarathy S

                  WITH T_PLAN(TRAIN_NO  ,  TIME_FROM ,   TIME_UNTIL ,   RAIL_NO) AS (

                  SELECT 172,'07:06','15:50', 1 FROM DUAL UNION ALL

                  SELECT 120,'07:33','15:41', 2 FROM DUAL UNION ALL

                  SELECT 229,'07:56','14:52', 3 FROM DUAL UNION ALL

                  SELECT 217,'15:05','18:47', 1 FROM DUAL UNION ALL

                  SELECT 189,'16:10','19:53', 2 FROM DUAL UNION ALL

                  SELECT 185,'16:10','19:53', 3 FROM DUAL UNION ALL

                  SELECT 181,'20:10','23:53', 2 FROM DUAL UNION ALL

                  SELECT 142,'20:14','23:32', 3 FROM DUAL)

                  SELECT T.TRAIN_NO,

                         T.TIME_FROM,

                         T.TIME_UNTIL,

                         T.RAIL_NO,

                         CASE WHEN (T.TRAIN_NO IN (SELECT T1.TRAIN_NO FROM T_PLAN T1,

                       T_PLAN T2

                  WHERE (TO_TIMESTAMP(T1.TIME_FROM,'HH24:MI') BETWEEN TO_TIMESTAMP(T2.TIME_FROM,'HH24:MI') AND TO_TIMESTAMP(T2.TIME_UNTIL,'HH24:MI')

                  OR    TO_TIMESTAMP(T1.TIME_UNTIL,'HH24:MI') BETWEEN TO_TIMESTAMP(T2.TIME_FROM,'HH24:MI') AND TO_TIMESTAMP(T2.TIME_UNTIL,'HH24:MI'))

                  AND T1.TRAIN_NO != T2.TRAIN_NO

                  AND T1.RAIL_NO = T2.RAIL_NO)) THEN 'INVALID' ELSE 'VALID' END AS CHECK_NOW

                  FROM T_PLAN T;

                   

                  OUTPUT:

                    TRAIN_NO TIME_ TIME_    RAIL_NO CHECK_N
                  ---------- ----- ----- ---------- -------
                         172 07:06 15:50          1 INVALID
                         120 07:33 15:41          2 VALID
                         229 07:56 14:52          3 VALID
                         217 15:05 18:47          1 INVALID
                         189 16:10 19:53          2 VALID
                         185 16:10 19:53          3 VALID
                         181 20:10 23:53          2 VALID
                         142 20:14 23:32          3 VALID

                  8 rows selected.

                   

                  Message was edited by: Parth272025

                  1 person found this helpful
                  • 7. Re: Check crossing time periods between rows
                    Stew Ashton

                    I agree that LAG and LEAD are not enough, but it can be done with other analytical functions:

                     

                    WITH d AS (
                      SELECT 172 train, to_date('07:06','hh24:mi') AS sd, to_date('15:50','hh24:mi') AS ed, 1 rail FROM dual UNION ALL
                      select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all
                      select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all
                      SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all
                      select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all
                      SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all
                      SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL
                      SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual
                    )
                    select train, sd, ed, rail,
                    case when ed > next_sd or sd < prev_ed then 'INVALID'
                         else 'VALID'
                    end check_row
                    from (
                      select d.*,
                      min(sd) over(partition by rail order by sd rows between 1 following and unbounded following) next_sd,
                      max(ed) over(partition by rail order by ed rows between unbounded preceding and 1 preceding) prev_ed
                      from d
                    )
                    order by sd;
                    

                     

                    TRAINSDEDRAILCHECK_ROW
                    1722014-02-01 07:06:002014-02-01 15:50:001INVALID
                    1202014-02-01 07:33:002014-02-01 15:41:002VALID
                    2292014-02-01 07:56:002014-02-01 14:52:003VALID
                    2172014-02-01 15:05:002014-02-01 18:47:001INVALID
                    1892014-02-01 16:10:002014-02-01 19:53:002VALID
                    1852014-02-01 16:10:002014-02-01 19:53:003VALID
                    1812014-02-01 20:10:002014-02-01 23:53:002VALID
                    1422014-02-01 20:14:002014-02-01 23:32:003VALID
                    1 person found this helpful
                    • 8. Re: Check crossing time periods between rows
                      Tobias Arnhold

                      Thanks. Exactly what I was looking for.

                      • 9. Re: Check crossing time periods between rows
                        Stew Ashton

                        Here's an alternative for when we all have 12c. I added a line to create multiple overlaps.

                         

                        WITH d AS (
                          SELECT 172 train, to_date('07:06','hh24:mi') AS sd, to_date('15:50','hh24:mi') AS ed, 1 rail FROM dual UNION ALL
                          select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all
                          select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all
                          SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all
                          SELECT 218,to_date('15:10','hh24:mi'),to_date('19:47','hh24:mi') as ed, 1 rail from dual union all
                          select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all
                          SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all
                          SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL
                          SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual
                        )
                        select train, sd, ed, rail,
                        nvl2(mn, 'INVALID', 'VALID') check_row
                        from d
                        match_recognize(
                          partition by rail order by sd
                          measures match_number() mn
                          all rows per match with unmatched rows
                          pattern (a b+)
                          define b as b.sd < a.ed
                        )
                        order by sd;
                        

                         

                        TRAINSDEDRAILCHECK_ROW
                        1722014-02-01 07:06:002014-02-01 15:50:001INVALID
                        1202014-02-01 07:33:002014-02-01 15:41:002VALID
                        2292014-02-01 07:56:002014-02-01 14:52:003VALID
                        2172014-02-01 15:05:002014-02-01 18:47:001INVALID
                        2182014-02-01 15:10:002014-02-01 19:47:001INVALID
                        1892014-02-01 16:10:002014-02-01 19:53:002VALID
                        1852014-02-01 16:10:002014-02-01 19:53:003VALID
                        1812014-02-01 20:10:002014-02-01 23:53:002VALID
                        1422014-02-01 20:14:002014-02-01 23:32:003VALID