Algorithm and sql ways for Sorting and matching data

Database
Enthusiast

Algorithm and sql ways for Sorting and matching data

Hi,

I'm looking for algorithm and guidance, and i will try to write my sql. Any help is much appreciated..

Say i've following data.

Product Event DateTime 
P1 Es1 T1
P1 Es2 T2
P1 Ec1 T3
P1 Es2 T4
P1 Es3 T5
P1 Ec2 T6
P2 Es1 T1
P2 Es1 T2
P2 Ec3 T3
P3 Es1 T4
P3 Es2 T5
P3 Ec1 T6
P4 Es1 T1
P4 Ec1 T2
P4 Ec2 T3
P4 Es1 T5
P4 Es2 T6
P5 Ec1 T7
P6 Ec2 T8

A Product can go through multiple events at different times. There are certain starting events denoted by Es1, Es2, Es3 and closing events denoted by Ec1,Ec3 and Ec3. I'm trying to come up with an algorithm or SQL to find the number of starting events followed by clsoing events for each product.

In the above case,  Product P1 has 2 cycles,, one starts at T1 (with event Es1) and ends T3 (Ec1) the other starts at T4(Es2) and ends at T6(Ec2) and the corresponding time duration of each cycle..

Similarly for P2 - T1 to T3, P3 - T4 to T6

and for P4 - one from T1 to T3 and the other T5 and T6 under P4 are unresolved as there is no ther closing event after them..

P6 can be ignored as there is starting event for them earlier than those even times under P6..

I hope this is clear, and suggestions are much appreciated

3 REPLIES
Enthusiast

Re: Algorithm and sql ways for Sorting and matching data

looking up tp dnoeth or any other experts who can help me out here !!

Senior Apprentice

Re: Algorithm and sql ways for Sorting and matching data

You need to explain in more detail on which rules those cycles are based.

Dieter

Enthusiast

Re: Algorithm and sql ways for Sorting and matching data

Hi,

Your problem seems weird. When I see at P2, I can see Es1 two times. Maybe you can explain  more about the problems till the end and what are those conditions.

Meanwhile maybe you can think of this way but you need to be in TD 13.10 and above.

You can use min , max and partiton by product order by datetime reset when that column meeting a condition rows unbounded preceding) and then use where clause. I dont have Teradata access else I could have tried.

Cheers,

Raja