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

Tags (4)
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 !!

Junior Contributor

## 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 &nbsp;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