Forum Stats

  • 3,728,472 Users
  • 2,245,631 Discussions
  • 7,853,549 Comments

Discussions

Check crossing time periods between rows

Tobias Arnhold
Tobias Arnhold Member Posts: 634
edited February 2014 in SQL & PL/SQL

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

Tagged:
Hoek

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond
    edited February 2014 Accepted Answer

    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?

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond
    edited February 2014 Accepted Answer

    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?

  • RogerT
    RogerT Member Posts: 1,852 Gold Trophy

    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)          

    RogerT
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond

    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.

    Frank Kulash
  • RogerT
    RogerT Member Posts: 1,852 Gold Trophy
  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy

    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
    
    Nimish Garg
  • Partha Sarathy S
    Partha Sarathy S Member Posts: 1,200
    edited February 2014
    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

    Partha Sarathy S
  • Stew Ashton
    Stew Ashton Member Posts: 2,849 Gold Trophy

    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
    Stew Ashton
  • Tobias Arnhold
    Tobias Arnhold Member Posts: 634

    Thanks. Exactly what I was looking for.

  • Stew Ashton
    Stew Ashton Member Posts: 2,849 Gold Trophy

    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
    Hoek
This discussion has been closed.