While loop for Teradata

Question

world.

I'm try to find a way to compile multiple events together. Data looks like this:

Basically, it is series of row data with event logs

I want to generate an aggregation of these row events such that if a new event occurred within 30 seconds of the other ending, it combines the time together. However, if the event log does not have an abutting event, then it is not captured. And these events are 'person' specific.

I envision the output to look something like this:

My intuition suggests using some kind of while loop, but I'm not sure where to start

dnoeth · Accepted Answer · 2020-01-16 15:41:31Z

There's no need for recursion (and very hard to write) or a loop over a cursor.

SELECT Person, Min(starttime), Max(starttime), -- get a concatenated string Trim(Trailing ',' FROM (XmlAgg(Reason || ',' ORDER BY Reason ) (VARCHAR(1000)))) FROM ( SELECT Person, Start_timestamp, Stop_timestamp, Reason, -- assign the same number to all rows within 30 seconds Sum(flag) Over Over (PARTITION BY Person ORDER BY Start_timestamp ROWS Unbounded Preceding) AS grp FROM ( SELECT Person, Start_timestamp, Stop_timestamp, Reason, -- check if previous end is within 30 seconds of the current start CASE WHEN Lag(Stop_timestamp) Over (PARTITION BY Person ORDER BY Start_timestamp) + INTERVAL '30' SECOND < Start_timestamp THEN 0 ELSE 1 END AS flag FROM tab ) AS dt ) AS dt -- aggregate per person and group GROUP BY Person, grp

If your Teradata version supports SESSIONIZE you can simplify the group calculation, but I couldn't write this syntaxc ad hoc :-)

This works pretty close to what I'm hoping for. Thank you dnoeth.

Arun Palanisamy · Accepted Answer · 2020-01-16 06:51:36Z

You can achieve this using RECURSIVE CTE

WITH RECURSIVE MYREC(Person,Start_timestamp,Stop_timestamp ,Reason,LVL) AS( SELECT Person,MIN(Start_timestamp),MAX(Stop_timestamp),MIN(Reason)(varchar(100)) AS Reason,1 FROM MYTABLE GROUP BY 1 UNION ALL SELECT b.Person,b.Start_timestamp,b.Stop_timestamp ,trim(a.Reason) || ',' || trim( b.Reason), LVL+1 FROM MYTABLE a INNER JOIN MYREC b ON a.Person = b.Person AND a.Reason > b.Reason ) SELECT Person,Start_timestamp,Stop_timestamp,Reason FROM MYREC QUALIFY RANK() OVER(PARTITION BY Person ORDER BY Reason DESC) = 1

Change MYTABLE to your tablename

Collectives™ on Stack Overflow

While loop for Teradata

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related