Identifying values from previous record

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Visitor

Identifying values from previous record

Hello, 

 

I have data similar to the following, ordered by start date.  For a given node, I want to evaluate the start and end times associated with each instance of the node.

 

Node .   | .  Start Time .   | .    End Time .    | XValue

1 .          | . 2:00 .             |   3:00                 | . ABC

1 .          | . 2:15 .             |   2:45                 | . DEF

1            | . 3:05               | . 4:00                 |   HIJ

 

 

I would like to update a new field in my query that, if the start time and end time are within the value set of the previous record,  would be set to the Xvalue of the previous record.  In the example above, I would expect the value of this new field would be set to ABC on Row 2, and blank in the other rows.

 

Any help would be greatly appreciated.  Thanks so much. 

 

 


Accepted Solutions
Ambassador

Re: Identifying values from previous record

Using OVERLAPS on begin/end is not really needed, if the end of the previous row is greater than [or equal to] (depending on the logic) the current start, both rows overlap :-)

1 ACCEPTED SOLUTION
3 REPLIES 3
Teradata Employee

Re: Identifying values from previous record

Hi sfurstar,

 

Considering the couple (Node, Start_Time) is unique, you can build a solution around this.

 

Data

create multiset volatile table mvt_data, no log
( col_node      byteint
, start_time    time(0)
, end_time      time(0)
, XValue        char(3)
, YValue        char(3)
)
primary index (col_node)
on commit preserve rows;

insert into mvt_data (col_node, start_time, end_time, XValue) values (1, time '02:00:00', time '03:00:00', 'ABC');
insert into mvt_data (col_node, start_time, end_time, XValue) values (1, time '02:15:00', time '02:45:00', 'DEF');
insert into mvt_data (col_node, start_time, end_time, XValue) values (1, time '03:05:00', time '04:00:00', 'HIJ');

collect statistics column (col_node) on mvt_data;

 

Select query

select col_node, start_time, end_time, XValue
     , case
          when period(min(start_time) over(partition by col_node order by start_time asc rows between 1 preceding and 1 preceding),
                      min(end_time)   over(partition by col_node order by start_time asc rows between 1 preceding and 1 preceding))
               overlaps period(start_time, end_time)
          then min(XValue) over(partition by col_node order by start_time asc rows between 1 preceding and 1 preceding)
       end as YValue
  from mvt_data
 where col_node = 1;

col_node  start_time  end_time  XValue  YValue
--------  ----------  --------  ------  ------
       1  02:00:00    03:00:00  ABC
       1  02:15:00    02:45:00  DEF     ABC
       1  03:05:00    04:00:00  HIJ

 

Update query

merge into mvt_data as tgt
 using (select col_node, start_time
             , case
                  when period(min(start_time) over(partition by col_node order by start_time asc rows between 1 preceding and 1 preceding),
                              min(end_time)   over(partition by col_node order by start_time asc rows between 1 preceding and 1 preceding))
                       overlaps period(start_time, end_time)
                  then min(XValue) over(partition by col_node order by start_time asc rows between 1 preceding and 1 preceding)
               end as YValue_c
          from mvt_data
       qualify YValue_c is not null) as src
    on tgt.col_node   = src.col_node
   and tgt.start_time = src.start_time
  when matched then update
   set YValue         = src.YValue_c;
-- MERGE completed. 1 rows processed.

select * from mvt_data
 where col_node = 1;

col_node  start_time  end_time  XValue  YValue
--------  ----------  --------  ------  ------
       1  02:00:00    03:00:00  ABC
       1  02:15:00    02:45:00  DEF     ABC
       1  03:05:00    04:00:00  HIJ

 

 

 

Ambassador

Re: Identifying values from previous record

Using OVERLAPS on begin/end is not really needed, if the end of the previous row is greater than [or equal to] (depending on the logic) the current start, both rows overlap :-)

Teradata Employee

Re: Identifying values from previous record

Yeah, easier :)

 

For fun, the nPath query for identifying rows :

 

  select col_node   as col_node
       , start_time as start_time
       , end_time   as end_time
       , XValue     as XValue
       , YValue     as YValue
    from nPath(          on mvt_data
               partition by col_node
                   order by start_time asc
                      using
                       mode ( nonoverlapping )
                    symbols ( start_time <= lag(end_time, 1) as O
                            , True as E
                            )
                    pattern ( 'E.O' )
                     result ( first(col_node   of E) as col_node
                            , first(start_time of O) as start_time
                            , first(end_time   of O) as end_time
                            , first(XValue     of O) as XValue
                            , first(XValue     of E) as YValue
                            )
              ) as np
order by start_time asc;

col_node  start_time  end_time  XValue  YValue
--------  ----------  --------  ------  ------
       1  02:15:00    02:45:00  DEF     ABC