## Forum Stats

• 3,728,472 Users
• 2,245,631 Discussions

Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# Check crossing time periods between rows

Member Posts: 634
edited February 2014

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

Cheers Tobias

Tagged:

• Member, Moderator Posts: 40,322 Red Diamond

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?

• Member, Moderator Posts: 40,322 Red Diamond

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?

• 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)

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

• Member Posts: 1,852 Gold Trophy

Good point

• 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
```
• 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

• 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
• Member Posts: 634

Thanks. Exactly what I was looking for.

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