All Forums Database
webtduser 5 posts Joined 11/13
04 Nov 2013
Algorithm and sql ways for Sorting and matching data

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

webtduser 5 posts Joined 11/13
06 Nov 2013

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

dnoeth 4628 posts Joined 11/04
06 Nov 2013

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


Raja_KT 1246 posts Joined 07/09
07 Nov 2013


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.






Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.