Return row where values change and modify timestamp

Database

Return row where values change and modify timestamp

 

I'm writing a query that returns rows where the value in certain columns changes. This is the sql I currently have:

 

select id, num, cd1, cd2, cd3, start_timestamp, end_timestamp

from table

where id = 1 and num > 20

order by num

 

Below is a generic example of what my query is returning:

 

id      num          cd1       cd2       cd3                 start timestamp                        end timestamp                        

1         21           03         54         21              2016-03-01 12:34:56                2016-03-03 12:34:56 

1         22           03         54         21              2016-03-05 12:34:56                             null                 

1         23           03         54         21              2016-03-06 12:34:56                2016-03-06 12:34:56               

1         24           02         11         43              2017-03-02 12:34:56                2017-03-03 12:34:56 

1         25           02         11         43              2017-03-11 12:34:56                2017-03-12 12:34:56

1         26           05         76         61              2017-03-13 12:34:56                2017-03-14 12:34:56 

1         27           05         76         61              2017-03-15 12:34:56                             null 

1         28           05         76         61              2017-03-17 12:34:56                2017-03-18 12:34:56 

 

I need to only return rows where the cd1,cd2,cd3 column values change (eg. when 03 54 21 changes to 02 11 43). Also, the first records start timestamp needs to be 1900-01-01 and the current records end timestamp needs to be 2099-12-31.

 

id          cd1         cd2       cd3                 start timestamp                        end timestamp   

1             03         54         21              1900-01-01 12:34:56                  2017-03-01 12:34:55  (one second less)              

1             02         11         43              2017-03-01 12:34:56                 2017-03-13 12:34:55  (one second less)

1             05         76         61              2017-03-13 12:34:56                 2099-12-31 23:59:59

 

I've looked at case statements and olap but I don't really understand how to implement either. Any advice would be much appreciated.

4 REPLIES
Teradata Employee

Re: Return row where values change and modify timestamp

This data does not appear to me as something that requires CASE statements or windowing functions. Simple aggregation should work.  You might need to cast the time as char, but here's a start:

 

select id, num, cd1, cd2, cd3
    ,cast('1900-01-01 ' cast(min(start_timestamp) as time(0)) as timestamp(0))
    ,max(end_timestamp) - interval '1' second
from ...
group by id, num, cd1, cd2, cd3

Re: Return row where values change and modify timestamp

Thank you for your response. I've tried adjusting the case statement, but I keep getting a syntax error between '1900-01-01 ' and cast().

 

Teradata Employee

Re: Return row where values change and modify timestamp

That was just a draft to give you the idea. Like I said, you need to get more specific with the casting.  This should work, unless you also need to change the format or specify a format clause for the generated timestamp.

select id, num, cd1, cd2, cd3
	,cast('1900-01-01 ' || cast(min(start_timestamp) as time(0) format'hh:mi:ss') as char(8)) as timestamp(0))
	,max(end_timestamp) - interval '1' second
from ...
group by id, num, cd1, cd2, cd3
Teradata Employee

Re: Return row where values change and modify timestamp

One problem with using simple GROUP BY would be that if a values change and then later change back to prior values, you won't pick up all the changes.

Using nested "derived table" query, OLAP, and CASE:

SELECT id, cd1, cd2, cd3, start_timestamp, 
/* back up one "tick" from next start, or use high timestamp if this is the last row */
COALESCE(PRIOR(MAX(start_timestamp) OVER (PARTITION BY ID ORDER BY start_timestamp ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING))
,timestamp'2099-12-31 23:59:59') AS end_timestamp FROM ( SELECT id, cd1, cd2, cd3, /* If this is the first record for the ID, return low timestamp */ CASE WHEN MAX(id) OVER (PARTITION BY id ORDER BY num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) IS NULL THEN timestamp'1900-01-01 00:00:00' ELSE start_timestamp END AS start_timestamp FROM table QUALIFY /* only when this is the first row for the id, or something else changed */ MAX(id) OVER (PARTITION BY id ORDER BY num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) IS NULL OR cd1 <> MAX(cd1) OVER (PARTITION BY id ORDER BY num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) OR cd2 <> MAX(cd2) OVER (PARTITION BY id ORDER BY num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) OR cd3 <> MAX(cd3) OVER (PARTITION BY id ORDER BY num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) ) ChangesOnly

The MAX (or MIN) function is a syntax requirement, but note we are really only picking up the value from one row. If you are on TD16 you can use LEAD/LAG instead.