Database
Senior Supporter

## Algorithm and sql ways for Sorting and matching data II

Hi,

I was not able to post to the orig post at

http://forums.teradata.com/forum/database/algorithm-and-sql-ways-for-sorting-and-matching-data#comme...

So here a try to overcome the techical problem.

some nested OLAP functions will do the trick.

But please provide the next time the DDLs and Inserts for your problem. It makes it easier for people to give you answers.

Ulrich

`create table txn_test (product char(2),  event   char(3),  event_dttm timestamp(0) ) primary index (product) ; Insert into txn_test values ('P1','Es1',current_timestamp(0) + Interval '1' day);Insert into txn_test values ('P1','Es2',current_timestamp(0) + Interval '2' day);Insert into txn_test values ('P1','Ec1',current_timestamp(0) + Interval '3' day);Insert into txn_test values ('P1','Es2',current_timestamp(0) + Interval '4' day);Insert into txn_test values ('P1','Es3',current_timestamp(0) + Interval '5' day);Insert into txn_test values ('P1','Ec2',current_timestamp(0) + Interval '6' day);Insert into txn_test values ('P2','Es1',current_timestamp(0) + Interval '1' day);Insert into txn_test values ('P2','Es1',current_timestamp(0) + Interval '2' day);Insert into txn_test values ('P2','Ec3',current_timestamp(0) + Interval '3' day);Insert into txn_test values ('P3','Es1',current_timestamp(0) + Interval '4' day);Insert into txn_test values ('P3','Es2',current_timestamp(0) + Interval '5' day);Insert into txn_test values ('P3','Ec1',current_timestamp(0) + Interval '6' day);Insert into txn_test values ('P4','Es1',current_timestamp(0) + Interval '1' day);Insert into txn_test values ('P4','Ec1',current_timestamp(0) + Interval '2' day);Insert into txn_test values ('P4','Ec2',current_timestamp(0) + Interval '3' day);Insert into txn_test values ('P4','Es1',current_timestamp(0) + Interval '5' day);Insert into txn_test values ('P4','Es2',current_timestamp(0) + Interval '6' day);Insert into txn_test values ('P5','Ec1',current_timestamp(0) + Interval '7' day);Insert into txn_test values ('P6','Ec2',current_timestamp(0) + Interval '8' day);select product,        min(event_dttm) over (partition by product, start_event_id) as event_start_dttm,       max(event_dttm) over (partition by product, start_event_id) as event_end_dttmfrom  ( select product,      event_dttm,      event_type,      start_event_ind,      end_event_ind,      sum(start_event_ind) over (partition by product order by event_dttm rows between unbounded preceding and current row) as start_event_id,     sum(end_event_ind) over (partition by product order by event_dttm rows between unbounded preceding and current row) as end_event_id from   (  select product,       case when event like 'ES%' then 'start' else 'end' end as event_type,       event_dttm,      case when event_type = 'start' and coalesce(min(event_type) over (partition by product order by event_dttm rows between 1 preceding and 1 preceding),'NON') <> 'start' then 1 else 0 end as start_event_ind,      case when event_type = 'end' and coalesce(min(event_type) over (partition by product order by event_dttm rows between 1 following and 1 following),'NON') <> 'end' then 1 else 0 end as end_event_ind  from txn_test  ) as t1 ) as t2qualify start_event_id = end_event_idorder by product,        event_dttm;`