OraFAQ Forum: SQL & PL/SQL » Corrections with overlapping time frames (SOLVED)

Home » SQL & PL/SQL » SQL & PL/SQL » Corrections with overlapping time frames (SOLVED) (Oracle 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Corrections with overlapping time frames (SOLVED) [message #647140]

Wed, 20 January 2016 07:16

quirks
Messages: 85
Registered: October 2014

Member

I've got a tricky Problem (at least it seems to me like one).

There are two tables. In the first table (T1) is shown how a value develops within a time.

T1)

KEY| VALID_FROM| VALID_TO  |VALUE
---|-----------|-----------|------
  A| 01.01.2016| 08.01.2016|    1
  A| 09.01.2016| 14.01.2016|    2
  A| 16.01.2016| 19.01.2016|    3

Now I got a second table (T2) which contains corrections for table one:

T2)

KEY| VALID_FROM| VALID_TO  |VALUE
---|-----------|-----------|------
  A| 03.01.2016| 04.01.2016|    4
  A| 07.01.2016| 10.01.2016|    5
  A| 13.01.2016| 14.01.2016|    6
  A| 16.01.2016| 17.01.2016|    7

What I want to archive is an SQL, that when there is a finding in T2 that matches the key and lies within the time frame of T1 it should replace the values in T1. But whenever there is no Value in T2 for a given time frame the values of T1 must "shine through".

I know my explanation is a little bit confusing, so here is a little picture (I hope it helps):
/forum/fa/12974/0/

This is how the expected output should look like.

KEY| VALID_FROM| VALID_TO  |VALUE
---|-----------|-----------|------
  A| 01.01.2016| 02.01.2016|    1
  A| 03.01.2016| 04.01.2016|    4
  A| 05.01.2016| 06.01.2016|    1
  A| 07.01.2016| 10.01.2016|    5
  A| 11.01.2016| 12.01.2016|    2
  A| 13.01.2016| 14.01.2016|    6
  A| 16.01.2016| 17.01.2016|    7
  A| 18.01.2016| 19.01.2016|    3

Here is a little script to create the two tables so you have something to experiment with:

Toggle Spoiler

I've mangled my mind, but cannot find a working approach. Does anyone of you got an idea how to achieve my goal? Any hint is helpful.

Thanks in advance and best regards
quirks

[mod-edit: marked as solved (in title) by bb per o.p. request]

Attachment: intersect time frames.png
(Size: 15.48KB, Downloaded 1583 times)

[Updated on: Mon, 25 January 2016 13:21] by Moderator

Report message to a moderator

Re: Corrections with overlapping time frames [message #647159 is a reply to message #647140]

Wed, 20 January 2016 17:27

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

The following seems to work. There may be a better way to do it using SQL instead of PL/SQL.

SCOTT@orcl> SELECT * FROM t1 ORDER BY valid_from
  2  /

K VALID_FROM VALID_TO        VALUE
- ---------- ---------- ----------
A 01.01.2016 08.01.2016          1
A 09.01.2016 14.01.2016          2
A 16.01.2016 19.01.2016          3

3 rows selected.

SCOTT@orcl> SELECT * FROM t2 ORDER BY valid_from
  2  /

K VALID_FROM VALID_TO        VALUE
- ---------- ---------- ----------
A 03.01.2016 04.01.2016          4
A 07.01.2016 10.01.2016          5
A 13.01.2016 14.01.2016          6
A 16.01.2016 17.01.2016          7

4 rows selected.

SCOTT@orcl> DECLARE
  2    v_key	     t1.key%TYPE;
  3    v_valid_from  t1.valid_from%TYPE;
  4    v_valid_to    t1.valid_to%TYPE;
  5    v_value	     t1.value%TYPE;
  6    v_tab	     VARCHAR2(2);
  7  BEGIN
  8    DBMS_OUTPUT.PUT_LINE ('KEY| VALID_FROM| VALID_TO  |VALUE');
  9    DBMS_OUTPUT.PUT_LINE ('---|-----------|-----------|------');
 10    FOR r IN
 11  	 (SELECT key, valid_from, valid_to, value, 't1' tab FROM t1
 12  	  UNION ALL
 13  	  SELECT key, valid_from, valid_to, value, 't2' tab FROM t2
 14  	  ORDER  BY key, valid_from, tab)
 15    LOOP
 16  	 IF v_key IS NULL THEN
 17  	   IF r.tab = 't2' THEN
 18  	     DBMS_OUTPUT.PUT_LINE
 19  	       (RPAD (r.key, 3)
 20  	       || '  ' || TO_CHAR (r.valid_from, 'DD.MM.YYYY')
 21  	       || '  ' || TO_CHAR (r.valid_to, 'DD.MM.YYYY')
 22  	       || LPAD (r.value, 5));
 23  	   END IF;
 24  	   v_key := r.key;
 25  	   v_valid_from := r.valid_from;
 26  	   v_valid_to := r.valid_to;
 27  	   v_value := r.value;
 28  	   v_tab := r.tab;
 29  	 ELSIF v_key != r.key THEN
 30  	   DBMS_OUTPUT.PUT_LINE
 31  	     (RPAD (v_key, 3)
 32  	     || '  ' || TO_CHAR (v_valid_from, 'DD.MM.YYYY')
 33  	     || '  ' || TO_CHAR (v_valid_to, 'DD.MM.YYYY')
 34  	     || LPAD (v_value, 5));
 35  	   v_key := r.key;
 36  	   v_valid_from := r.valid_from;
 37  	   v_valid_to := r.valid_to;
 38  	   v_value := r.value;
 39  	   v_tab := r.tab;
 40  	 ELSIF r.tab = 't2' AND v_tab = 't2' THEN
 41  	   DBMS_OUTPUT.PUT_LINE
 42  	     (RPAD (r.key, 3)
 43  	      || '  ' || TO_CHAR (r.valid_from, 'DD.MM.YYYY')
 44  	      || '  ' || TO_CHAR (r.valid_to, 'DD.MM.YYYY')
 45  	      || LPAD (r.value, 5));
 46  	   v_key := r.key;
 47  	   v_valid_from := r.valid_from;
 48  	   v_valid_to := r.valid_to;
 49  	   v_value := r.value;
 50  	   v_tab := r.tab;
 51  	 ELSIF r.tab = 't2' THEN
 52  	   IF v_valid_from < r.valid_from THEN
 53  	     DBMS_OUTPUT.PUT_LINE
 54  	       (RPAD (v_key, 3)
 55  	       || '  ' || TO_CHAR (v_valid_from, 'DD.MM.YYYY')
 56  	       || '  ' || TO_CHAR (LEAST (v_valid_to, r.valid_from - 1), 'DD.MM.YYYY')
 57  	       || LPAD (v_value, 5));
 58  	   END IF;
 59  	   DBMS_OUTPUT.PUT_LINE
 60  	     (RPAD (r.key, 3)
 61  	     || '  ' || TO_CHAR (r.valid_from, 'DD.MM.YYYY')
 62  	     || '  ' || TO_CHAR (r.valid_to, 'DD.MM.YYYY')
 63  	     || LPAD (r.value, 5));
 64  	   IF v_valid_to > r.valid_to THEN
 65  	     v_valid_from := r.valid_to + 1;
 66  	   ELSE
 67  	     v_key := r.key;
 68  	     v_valid_from := r.valid_from;
 69  	     v_valid_to := r.valid_to;
 70  	     v_value := r.value;
 71  	     v_tab := r.tab;
 72  	   END IF;
 73  	 ELSIF v_tab = 't2' THEN
 74  	   IF v_valid_to < r.valid_to THEN
 75  	     v_key := r.key;
 76  	     v_valid_from := GREATEST (r.valid_from, v_valid_to + 1);
 77  	     v_valid_to := r.valid_to;
 78  	     v_value := r.value;
 79  	     v_tab := r.tab;
 80  	   END IF;
 81  	 ELSE
 82  	   v_key := r.key;
 83  	   v_valid_from := r.valid_from;
 84  	   v_valid_to := r.valid_to;
 85  	   v_value := r.value;
 86  	   v_tab := r.tab;
 87  	 END IF;
 88    END LOOP;
 89    IF v_tab != 't2' THEN
 90  	 DBMS_OUTPUT.PUT_LINE
 91  	   (RPAD (v_key, 3)
 92  	    || '  ' || TO_CHAR (v_valid_from, 'DD.MM.YYYY')
 93  	    || '  ' || TO_CHAR (v_valid_to, 'DD.MM.YYYY')
 94  	    || LPAD (v_value, 5));
 95    END IF;
 96  END;
 97  /
KEY| VALID_FROM| VALID_TO  |VALUE
---|-----------|-----------|------
A    01.01.2016  02.01.2016    1
A    03.01.2016  04.01.2016    4
A    05.01.2016  06.01.2016    1
A    07.01.2016  10.01.2016    5
A    11.01.2016  12.01.2016    2
A    13.01.2016  14.01.2016    6
A    16.01.2016  17.01.2016    7
A    18.01.2016  19.01.2016    3

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Corrections with overlapping time frames [message #647160 is a reply to message #647159]

Wed, 20 January 2016 18:26

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

It looks like this works in SQL, but there may still be a better way.

SCOTT@orcl> SELECT * FROM t1 ORDER BY valid_from
  2  /

K VALID_FROM VALID_TO        VALUE
- ---------- ---------- ----------
A 01.01.2016 08.01.2016          1
A 09.01.2016 14.01.2016          2
A 16.01.2016 19.01.2016          3

3 rows selected.

SCOTT@orcl> SELECT * FROM t2 ORDER BY valid_from
  2  /

K VALID_FROM VALID_TO        VALUE
- ---------- ---------- ----------
A 03.01.2016 04.01.2016          4
A 07.01.2016 10.01.2016          5
A 13.01.2016 14.01.2016          6
A 16.01.2016 17.01.2016          7

4 rows selected.

SCOTT@orcl> SELECT key,
  2  	    DECODE (tab, 't2', valid_from, GREATEST (valid_from, NVL (lag_to + 1, valid_from))) valid_from,
  3  	    DECODE (tab, 't2', valid_to, LEAST (valid_to, NVL (lead_from - 1, valid_to))) valid_to,
  4  	    value
  5  FROM   (SELECT key, valid_from, valid_to, value, tab,
  6  		    LAG (valid_to) OVER (PARTITION BY key ORDER BY valid_from, tab DESC) lag_to,
  7  		    LEAD (valid_from) OVER (PARTITION BY key ORDER BY valid_from, tab DESC) lead_from
  8  	     FROM   (SELECT key, valid_from, valid_to, value, 't1' tab FROM t1
  9  		     UNION ALL
 10  		     SELECT key, valid_from, valid_to, value, 't2' tab FROM t2))
 11  UNION
 12  SELECT key,
 13  	    DECODE (tab, 't2', valid_from, GREATEST (valid_from, NVL (lead_to + 1, valid_from))) valid_from,
 14  	    DECODE (tab, 't2', valid_to, LEAST (valid_to, NVL (lag_from - 1, valid_to))) valid_to,
 15  	    value
 16  FROM   (SELECT key, valid_from, valid_to, value, tab,
 17  		    LEAD (valid_to) OVER (PARTITION BY key ORDER BY valid_to DESC, tab DESC) lead_to,
 18  		    LAG (valid_from) OVER (PARTITION BY key ORDER BY valid_to DESC, tab DESC) lag_from
 19  	     FROM   (SELECT key, valid_from, valid_to, value, 't1' tab FROM t1
 20  		     UNION ALL
 21  		     SELECT key, valid_from, valid_to, value, 't2' tab FROM t2))
 22  ORDER  BY key, valid_from
 23  /

K VALID_FROM VALID_TO        VALUE
- ---------- ---------- ----------
A 01.01.2016 02.01.2016          1
A 03.01.2016 04.01.2016          4
A 05.01.2016 06.01.2016          1
A 07.01.2016 10.01.2016          5
A 11.01.2016 12.01.2016          2
A 13.01.2016 14.01.2016          6
A 16.01.2016 17.01.2016          7
A 18.01.2016 19.01.2016          3

8 rows selected.

Report message to a moderator

Re: Corrections with overlapping time frames [message #647161 is a reply to message #647140]

Wed, 20 January 2016 18:59

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

SQL solution:

with x1 as (
             select  t1.*,
                     0 weight
               from  t1
            union all
             select  t2.*,
                     1 weight
               from  t2
           ),
     x2 as (
             select  distinct key,
                              valid_from dt,
                              1 offset
               from  x1
           ),
     x3 as (
             select  distinct key,
                              valid_to dt,
                              0 offset
               from  x1
           ),
     x4 as (
             select  *
               from  x2
            union all
             select  *
               from  x3
           ),
     x5 as (
            select  key,
                    dt + mod(offset + 1,2) valid_from,
                    lead(dt) over(partition by key order by dt) - lead(offset) over(partition by key order by dt) valid_to
              from  x4
           ),
     x6 as (
            select  x5.key,
                    x5.valid_from,
                    x5.valid_to,
                    max(x1.value) keep(dense_rank last order by x1.weight) value
              from  x5,
                    x1
              where x5.valid_from between x1.valid_from and x1.valid_to
                and x5.valid_to is not null
                and x5.valid_to >= x5.valid_from
              group by x5.key,
                       x5.valid_from,
                       x5.valid_to
           ),
     x7 as (
            select  key,
                    valid_from,
                    valid_to,
                    value,
                    case
                      when     lag(valid_to) over(partition by key order by valid_from) + 1 = valid_from
                           and
                               lag(value) over(partition by key order by valid_from) = value
                        then 0
                      else 1
                    end start_of_group
              from  x6
           ),
     x8 as (
            select  key,
                    valid_from,
                    valid_to,
                    value,
                    sum(start_of_group) over(partition by key order by valid_from) grp
              from  x7
           )
select  key,
        min(valid_from) valid_from,
        max(valid_to) valid_to,
        value
  from  x8
  group by key,
           grp,
           value
  order by key,
           valid_from
/

KEY VALID_FROM VALID_TO       VALUE
--- ---------- --------- ----------
A   01-JAN-16  02-JAN-16          1
A   03-JAN-16  04-JAN-16          4
A   05-JAN-16  06-JAN-16          1
A   07-JAN-16  10-JAN-16          5
A   11-JAN-16  12-JAN-16          2
A   13-JAN-16  14-JAN-16          6
A   16-JAN-16  17-JAN-16          7
A   18-JAN-16  19-JAN-16          3

8 rows selected.

SQL>

SY.

Report message to a moderator

Re: Corrections with overlapping time frames [message #647162 is a reply to message #647161]

Wed, 20 January 2016 21:21

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

I don't have a large data sample to compare times and I don't know what indexes the original poster may have, but just looking at the execution plans on the provided sample without indexes, I think mine looks a little less complex.

-- mine:

SCOTT@orcl> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl> SELECT key,
  2  	    DECODE (tab, 't2', valid_from, GREATEST (valid_from, NVL (lag_to + 1, valid_from))) valid_from,
  3  	    DECODE (tab, 't2', valid_to, LEAST (valid_to, NVL (lead_from - 1, valid_to))) valid_to,
  4  	    value
  5  FROM   (SELECT key, valid_from, valid_to, value, tab,
  6  		    LAG (valid_to) OVER (PARTITION BY key ORDER BY valid_from, tab DESC) lag_to,
  7  		    LEAD (valid_from) OVER (PARTITION BY key ORDER BY valid_from, tab DESC) lead_from
  8  	     FROM   (SELECT key, valid_from, valid_to, value, 't1' tab FROM t1
  9  		     UNION ALL
 10  		     SELECT key, valid_from, valid_to, value, 't2' tab FROM t2))
 11  UNION
 12  SELECT key,
 13  	    DECODE (tab, 't2', valid_from, GREATEST (valid_from, NVL (lead_to + 1, valid_from))) valid_from,
 14  	    DECODE (tab, 't2', valid_to, LEAST (valid_to, NVL (lag_from - 1, valid_to))) valid_to,
 15  	    value
 16  FROM   (SELECT key, valid_from, valid_to, value, tab,
 17  		    LEAD (valid_to) OVER (PARTITION BY key ORDER BY valid_to DESC, tab DESC) lead_to,
 18  		    LAG (valid_from) OVER (PARTITION BY key ORDER BY valid_to DESC, tab DESC) lag_from
 19  	     FROM   (SELECT key, valid_from, valid_to, value, 't1' tab FROM t1
 20  		     UNION ALL
 21  		     SELECT key, valid_from, valid_to, value, 't2' tab FROM t2))
 22  ORDER  BY key, valid_from
 23  /

K VALID_FROM VALID_TO        VALUE
- ---------- ---------- ----------
A 01.01.2016 02.01.2016          1
A 03.01.2016 04.01.2016          4
A 05.01.2016 06.01.2016          1
A 07.01.2016 10.01.2016          5
A 11.01.2016 12.01.2016          2
A 13.01.2016 14.01.2016          6
A 16.01.2016 17.01.2016          7
A 18.01.2016 19.01.2016          3

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2331597729

--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    14 |   798 |    17  (30)| 00:00:01 |
|   1 |  SORT UNIQUE            |      |    14 |   798 |    16  (25)| 00:00:01 |
|   2 |   UNION-ALL             |      |       |       |            |          |
|   3 |    VIEW                 |      |     7 |   399 |     7  (15)| 00:00:01 |
|   4 |     WINDOW SORT         |      |     7 |   273 |     7  (15)| 00:00:01 |
|   5 |      VIEW               |      |     7 |   273 |     6   (0)| 00:00:01 |
|   6 |       UNION-ALL         |      |       |       |            |          |
|   7 |        TABLE ACCESS FULL| T1   |     3 |   105 |     3   (0)| 00:00:01 |
|   8 |        TABLE ACCESS FULL| T2   |     4 |   140 |     3   (0)| 00:00:01 |
|   9 |    VIEW                 |      |     7 |   399 |     7  (15)| 00:00:01 |
|  10 |     WINDOW SORT         |      |     7 |   273 |     7  (15)| 00:00:01 |
|  11 |      VIEW               |      |     7 |   273 |     6   (0)| 00:00:01 |
|  12 |       UNION-ALL         |      |       |       |            |          |
|  13 |        TABLE ACCESS FULL| T1   |     3 |   105 |     3   (0)| 00:00:01 |
|  14 |        TABLE ACCESS FULL| T2   |     4 |   140 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

-- yours:

SCOTT@orcl> with x1 as (
  2  		  select  t1.*,
  3  			  0 weight
  4  		    from  t1
  5  		 union all
  6  		  select  t2.*,
  7  			  1 weight
  8  		    from  t2
  9  		),
 10  	  x2 as (
 11  		  select  distinct key,
 12  				   valid_from dt,
 13  				   1 offset
 14  		    from  x1
 15  		),
 16  	  x3 as (
 17  		  select  distinct key,
 18  				   valid_to dt,
 19  				   0 offset
 20  		    from  x1
 21  		),
 22  	  x4 as (
 23  		  select  *
 24  		    from  x2
 25  		 union all
 26  		  select  *
 27  		    from  x3
 28  		),
 29  	  x5 as (
 30  		 select  key,
 31  			 dt + mod(offset + 1,2) valid_from,
 32  			 lead(dt) over(partition by key order by dt) - lead(offset) over(partition by key order by dt) valid_to
 33  		   from  x4
 34  		),
 35  	  x6 as (
 36  		 select  x5.key,
 37  			 x5.valid_from,
 38  			 x5.valid_to,
 39  			 max(x1.value) keep(dense_rank last order by x1.weight) value
 40  		   from  x5,
 41  			 x1
 42  		   where x5.valid_from between x1.valid_from and x1.valid_to
 43  		     and x5.valid_to is not null
 44  		     and x5.valid_to >= x5.valid_from
 45  		   group by x5.key,
 46  			    x5.valid_from,
 47  			    x5.valid_to
 48  		),
 49  	  x7 as (
 50  		 select  key,
 51  			 valid_from,
 52  			 valid_to,
 53  			 value,
 54  			 case
 55  			   when     lag(valid_to) over(partition by key order by valid_from) + 1 = valid_from
 56  				and
 57  				    lag(value) over(partition by key order by valid_from) = value
 58  			     then 0
 59  			   else 1
 60  			 end start_of_group
 61  		   from  x6
 62  		),
 63  	  x8 as (
 64  		 select  key,
 65  			 valid_from,
 66  			 valid_to,
 67  			 value,
 68  			 sum(start_of_group) over(partition by key order by valid_from) grp
 69  		   from  x7
 70  		)
 71  select  key,
 72  	     min(valid_from) valid_from,
 73  	     max(valid_to) valid_to,
 74  	     value
 75    from  x8
 76    group by key,
 77  		grp,
 78  		value
 79    order by key,
 80  		valid_from
 81  /

K VALID_FROM VALID_TO        VALUE
- ---------- ---------- ----------
A 01.01.2016 02.01.2016          1
A 03.01.2016 04.01.2016          4
A 05.01.2016 06.01.2016          1
A 07.01.2016 10.01.2016          5
A 11.01.2016 12.01.2016          2
A 13.01.2016 14.01.2016          6
A 16.01.2016 17.01.2016          7
A 18.01.2016 19.01.2016          3

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4254873127

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                           |     1 |    42 |    22  (46)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION         |                           |       |       |            |          |
|   2 |   LOAD AS SELECT                   | SYS_TEMP_0FD9D6662_3051C6 |       |       |            |          |
|   3 |    UNION-ALL                       |                           |       |       |            |          |
|   4 |     TABLE ACCESS FULL              | T1                        |     3 |   105 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL              | T2                        |     4 |   140 |     3   (0)| 00:00:01 |
|   6 |   SORT ORDER BY                    |                           |     1 |    42 |    16  (63)| 00:00:01 |
|   7 |    HASH GROUP BY                   |                           |     1 |    42 |    16  (63)| 00:00:01 |
|   8 |     VIEW                           |                           |     1 |    42 |    14  (58)| 00:00:01 |
|   9 |      WINDOW SORT                   |                           |     1 |    32 |    14  (58)| 00:00:01 |
|  10 |       VIEW                         |                           |     1 |    32 |    13  (54)| 00:00:01 |
|  11 |        WINDOW SORT                 |                           |     1 |    50 |    13  (54)| 00:00:01 |
|  12 |         SORT GROUP BY              |                           |     1 |    50 |    13  (54)| 00:00:01 |
|  13 |          MERGE JOIN                |                           |     1 |    50 |    11  (46)| 00:00:01 |
|  14 |           SORT JOIN                |                           |     2 |    32 |     8  (50)| 00:00:01 |
|* 15 |            VIEW                    |                           |     2 |    32 |     7  (43)| 00:00:01 |
|  16 |             WINDOW SORT            |                           |     2 |    32 |     7  (43)| 00:00:01 |
|  17 |              VIEW                  |                           |     2 |    32 |     6  (34)| 00:00:01 |
|  18 |               UNION-ALL            |                           |       |       |            |          |
|  19 |                VIEW                |                           |     1 |    16 |     3  (34)| 00:00:01 |
|  20 |                 HASH UNIQUE        |                           |     1 |    13 |     3  (34)| 00:00:01 |
|  21 |                  VIEW              |                           |     7 |    91 |     2   (0)| 00:00:01 |
|  22 |                   TABLE ACCESS FULL| SYS_TEMP_0FD9D6662_3051C6 |     7 |   245 |     2   (0)| 00:00:01 |
|  23 |                VIEW                |                           |     1 |    15 |     3  (34)| 00:00:01 |
|  24 |                 HASH UNIQUE        |                           |     1 |    13 |     3  (34)| 00:00:01 |
|  25 |                  VIEW              |                           |     7 |    91 |     2   (0)| 00:00:01 |
|  26 |                   TABLE ACCESS FULL| SYS_TEMP_0FD9D6662_3051C6 |     7 |   245 |     2   (0)| 00:00:01 |
|* 27 |           FILTER                   |                           |       |       |            |          |
|* 28 |            SORT JOIN               |                           |     7 |   238 |     3  (34)| 00:00:01 |
|  29 |             VIEW                   |                           |     7 |   238 |     2   (0)| 00:00:01 |
|  30 |              TABLE ACCESS FULL     | SYS_TEMP_0FD9D6662_3051C6 |     7 |   245 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  15 - filter("X5"."VALID_TO" IS NOT NULL AND INTERNAL_FUNCTION("X5"."VALID_TO")>=INTERNAL_FUNCTION("X5"
              ."VALID_FROM"))
  27 - filter("X1"."VALID_TO">=INTERNAL_FUNCTION("X5"."VALID_FROM"))
  28 - access(INTERNAL_FUNCTION("X1"."VALID_FROM")<=INTERNAL_FUNCTION("X5"."VALID_FROM"))
       filter(INTERNAL_FUNCTION("X1"."VALID_FROM")<=INTERNAL_FUNCTION("X5"."VALID_FROM"))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SCOTT@orcl> SET AUTOTRACE OFF

Report message to a moderator

Re: Corrections with overlapping time frames [message #647212 is a reply to message #647162]

Fri, 22 January 2016 11:42

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

Hi Barbara,

There are some holes in your solution. For example, t2 completely overlaps t1:

drop table t1 purge
/
create table t1
  as
    select 'A' key,date '2016-01-01' valid_from,date '2016-01-08' valid_to,1 value from dual union all
    select 'A',date '2016-01-09',date '2016-01-14',2 from dual union all
    select 'A',date '2016-01-16',date '2016-01-19',3 from dual
/
drop table t2 purge
/
create table t2
  as
    select 'A' key,date '2015-12-03' valid_from,date '2016-01-24' valid_to,4 value from dual
/

Your solution:

 SELECT  key,
         DECODE(tab, 't2', valid_from, GREATEST (valid_from, NVL (lag_to + 1, valid_from))) valid_from,
         DECODE (tab, 't2', valid_to, LEAST (valid_to, NVL (lead_from - 1, valid_to))) valid_to,
         value
   FROM  (
          SELECT  key,
                  valid_from,
                  valid_to,
                  value,
                  tab,
                  LAG (valid_to) OVER (PARTITION BY key ORDER BY valid_from, tab DESC) lag_to,
                  LEAD (valid_from) OVER (PARTITION BY key ORDER BY valid_from, tab DESC) lead_from
            FROM  (
                    SELECT  key,
                            valid_from,
                            valid_to,
                            value,
                            't1' tab
                      FROM  t1
                   UNION ALL
                    SELECT  key,
                            valid_from,
                            valid_to,
                            value,
                            't2' tab FROM t2
                  )
         )
UNION
  SELECT key,
         DECODE (tab, 't2', valid_from, GREATEST (valid_from, NVL (lead_to + 1, valid_from))) valid_from,
         DECODE (tab, 't2', valid_to, LEAST (valid_to, NVL (lag_from - 1, valid_to))) valid_to,
         value
   FROM  (
          SELECT  key,
                  valid_from,
                  valid_to,
                  value,
                  tab,
                  LEAD (valid_to) OVER (PARTITION BY key ORDER BY valid_to DESC, tab DESC) lead_to,
                  LAG (valid_from) OVER (PARTITION BY key ORDER BY valid_to DESC, tab DESC) lag_from
            FROM  (
                    SELECT  key,
                            valid_from,
                            valid_to,
                            value, 't1' tab
                      FROM  t1
                   UNION ALL
                    SELECT  key,
                            valid_from,
                            valid_to,
                            value,
                            't2' tab
                      FROM  t2
                  )
         )
ORDER BY key,
         valid_from
/

KEY VALID_FROM VALID_TO       VALUE
--- ---------- --------- ----------
A   03-DEC-15  24-JAN-16          4
A   01-JAN-16  08-JAN-16          1
A   09-JAN-16  14-JAN-16          2
A   16-JAN-16  02-DEC-15          3
A   16-JAN-16  19-JAN-16          3
A   25-JAN-16  08-JAN-16          1

6 rows selected.

SQL>

My solution:

with x1 as (
             select  t1.*,
                     0 weight
               from  t1
            union all
             select  t2.*,
                     1 weight
               from  t2
           ),
     x2 as (
             select  distinct key,
                              valid_from dt,
                              1 offset
               from  x1
           ),
     x3 as (
             select  distinct key,
                              valid_to dt,
                              0 offset
               from  x1
           ),
     x4 as (
             select  *
               from  x2
            union all
             select  *
               from  x3
           ),
     x5 as (
            select  key,
                    dt + mod(offset + 1,2) valid_from,
                    lead(dt) over(partition by key order by dt) - lead(offset) over(partition by key order by dt) valid_to
              from  x4
           ),
     x6 as (
            select  x5.key,
                    x5.valid_from,
                    x5.valid_to,
                    max(x1.value) keep(dense_rank last order by x1.weight) value
              from  x5,
                    x1
              where x5.valid_from between x1.valid_from and x1.valid_to
                and x5.valid_to is not null
                and x5.valid_to >= x5.valid_from
              group by x5.key,
                       x5.valid_from,
                       x5.valid_to
           ),
     x7 as (
            select  key,
                    valid_from,
                    valid_to,
                    value,
                    case
                      when     lag(valid_to) over(partition by key order by valid_from) + 1 = valid_from
                           and
                               lag(value) over(partition by key order by valid_from) = value
                        then 0
                      else 1
                    end start_of_group
              from  x6
           ),
     x8 as (
            select  key,
                    valid_from,
                    valid_to,
                    value,
                    sum(start_of_group) over(partition by key order by valid_from) grp
              from  x7
           )
select  key,
        min(valid_from) valid_from,
        max(valid_to) valid_to,
        value
  from  x8
  group by key,
           grp,
           value
  order by key,
           valid_from
/

KEY VALID_FROM VALID_TO       VALUE
--- ---------- --------- ----------
A   03-DEC-15  24-JAN-16          4

SQL>

Or:

drop table t1 purge
/
create table t1
  as
    select 'A' key,date '2016-01-01' valid_from,date '2016-01-08' valid_to,1 value from dual union all
    select 'A',date '2016-01-09',date '2016-01-14',2 from dual union all
    select 'A',date '2016-01-16',date '2016-01-19',3 from dual
/
drop table t2 purge
/
create table t2
  as
    select 'A' key,date '2016-01-15' valid_from,date '2016-01-24' valid_to,4 value from dual
/

Your solution:


KEY VALID_FROM VALID_TO       VALUE
--- ---------- --------- ----------
A   01-JAN-16  08-JAN-16          1
A   09-JAN-16  14-JAN-16          2
A   15-JAN-16  24-JAN-16          4
A   16-JAN-16  14-JAN-16          3
A   25-JAN-16  19-JAN-16          3

SQL>

My solution:


KEY VALID_FROM VALID_TO       VALUE
--- ---------- --------- ----------
A   01-JAN-16  08-JAN-16          1
A   09-JAN-16  14-JAN-16          2
A   15-JAN-16  24-JAN-16          4

SQL>

SY.

Report message to a moderator

Re: Corrections with overlapping time frames [message #647216 is a reply to message #647212]

Fri, 22 January 2016 20:51

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Darn, I thought I had a good solution. Oh, well. At least the original poster has yours.

Report message to a moderator

Re: Corrections with overlapping time frames [message #647225 is a reply to message #647216]

Sat, 23 January 2016 05:43

quirks
Messages: 85
Registered: October 2014

Member

Hello,

first of all I'd like to thank you for your efforts. While playing around with your solutions, I figured, that my example does not cover all probable combinations. The missing ones are:

Entry in T1 / T2 has multiple matches in its time frame
Entry in T1 / T2 has a single match in its time frame
Entry in T1 / T2 has no matches.

Unfortunately I have currently no computer available (writing on my phone). But on monday I'll provide you with a new example.
Solomons solution might work for all cases (even if I could not figure out why^^).

Thanks again for your work. I wish you all a nice weekend. I'll get back to you on monday.

Cheers
Quirks

Report message to a moderator

Re: Corrections with overlapping time frames [message #647287 is a reply to message #647225]

Mon, 25 January 2016 07:04

quirks
Messages: 85
Registered: October 2014

Member

Hello,

I've tried to create an ex-sample (^^) where all situations are present (I probably have missed one or two).

Here is the Picture:
/forum/fa/12983/0/

Here are the Tables (find the create script in the spoiler).

T1)

KEY| VALID_FROM| VALID_TO  |VALUE
---|-----------|-----------|------
  A| 01/01/2016| 02/01/2016|   1
  A| 05/01/2016| 10/01/2016|   2
  A| 13/01/2016| 14/01/2016|   3
  A| 18/01/2016| 23/01/2016|   4
  A| 25/01/2016| 26/01/2016|   5
  A| 29/01/2016| 30/01/2016|   6
  A| 31/01/2016| 09/02/2016|   7
  A| 12/02/2016| 13/02/2016|   8
  A| 16/02/2016| 17/02/2016|   9

Toggle Spoiler

T2)

KEY| VALID_FROM| VALID_TO  |VALUE
---|-----------|-----------|------
  A| 03/01/2016| 04/01/2016|  10
  A| 07/01/2016| 08/01/2016|  11
  A| 11/01/2016| 16/01/2016|  12
  A| 18/01/2016| 19/01/2016|  13
  A| 22/01/2016| 23/01/2016|  14
  A| 25/01/2016| 30/01/2016|  15
  A| 02/02/2016| 03/02/2016|  16
  A| 06/02/2016| 07/02/2016|  17
  A| 10/02/2016| 19/02/2016|  18

Toggle Spoiler

This is the expected output:

Expected output)

KEY| VALID_FROM| VALID_TO  |VALUE
---|-----------|-----------|------
  A| 01/01/2016| 02/01/2016|   1
  A| 03/01/2016| 04/01/2016|  10
  A| 05/01/2016| 06/01/2016|   2
  A| 07/01/2016| 08/01/2016|  11
  A| 09/01/2016| 10/01/2016|   2
  A| 11/01/2016| 16/01/2016|  12
  A| 18/01/2016| 19/01/2016|  13
  A| 20/01/2016| 21/01/2016|   4
  A| 22/01/2016| 23/01/2016|  14
  A| 25/01/2016| 30/01/2016|  15
  A| 31/01/2016| 01/02/2016|   7
  A| 02/02/2016| 03/02/2016|  16
  A| 04/02/2016| 05/02/2016|   7
  A| 06/02/2016| 07/02/2016|  17
  A| 08/02/2016| 09/02/2016|   7
  A| 10/02/2016| 19/02/2016|  18

Solomons Solution works for this as well (I'll invest even more time in analyzing how it works). Wink

Even if I'd like to see different approaches to this problem (I think it might be teaching for me) this thread can be viewed as "solved".

Thanks again for your help
quirks

Attachment: intersect time frames.png
(Size: 24.55KB, Downloaded 1411 times)

[Updated on: Mon, 25 January 2016 07:20]

Report message to a moderator

Previous Topic:	help to make sql query
Next Topic:	How to get the first less date than agiven date in sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 11 07:03:36 CDT 2026