Algorithm and sql ways for Sorting and matching data II

Database
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_dttm
from
(
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 t2
qualify start_event_id = end_event_id
order by product,
event_dttm;