Finding next change in value

Database
Fan

Finding next change in value

Any suggestions on approaching this problem would be greatly appreciated! I have a table with time stamp information and another column with status. The status value will equal 173 when the device goes into a particular fault condition and the status will be set to value 166 when the fault has been cleared. What I am trying to do is find each occurrence of status=173, then find the next time stamp instance of status=166 and calculating the delta. This status change will change many times over time. Status 166 indicates normal operation, which means any status change can occur and then switch back to 166. So I need to look for the first instance of 166 after 173 and do that over the entire date/time range. Any recommendations on how to code that in SQL?

Thanks,

Fred
7 REPLIES
Junior Supporter

Re: Finding next change in value

This may not be the best way, but I think it does the job.

--OUTER STATEMENT:
SELECT pull_1.myTimeStamp
, pull_1.STATUS
FROM myDatabase.TEST_DEVICE pull_1
,(
--INNER STATEMENT:
Select myTimeStamp, STATUS
FROM myDatabase.TEST_DEVICE
WHERE STATUS = 173
) as pull_2
WHERE pull_1.myTimeStamp > pull_2.myTimeStamp
AND pull_1.STATUS = 166
GROUP BY pull_1.myTimeStamp, pull_1.STATUS
ORDER BY pull_1.myTimeStamp

Instead of using a date timestamp, I used an integer and called it myTimeStamp:

CREATE multiset TABLE myDatabase.TEST_DEVICE
(
myTimeStamp INTEGER not null
, STATUS INTEGER
)PRIMARY INDEX(myTimeStamp);

Test data:

INSERT INTO myDatabase.TEST_DEVICE
values( 1, 166);

INSERT INTO myDatabase.TEST_DEVICE
values( 2, 166);

INSERT INTO myDatabase.TEST_DEVICE
values( 3, 173);

INSERT INTO myDatabase.TEST_DEVICE
values( 4, 166);

INSERT INTO myDatabase.TEST_DEVICE
values( 5, 173);

INSERT INTO myDatabase.TEST_DEVICE
values( 6, 166);

INSERT INTO myDatabase.TEST_DEVICE
values( 7, 166);

INSERT INTO myDatabase.TEST_DEVICE
values( 8, 173);

INSERT INTO myDatabase.TEST_DEVICE
values( 9, 166);

Results:

myTimeStamp| STATUS
-----------|------------
4 |166
6 |166
7 |166
9 |166
Fan

Re: Finding next change in value

Thanks for your reply Teradatatester. One issue with your solution is that I am only looking for the first occurrence of status change to 166 following a status of 173. So based on your data set, I would want to extract :

Time | Status
4 | 166
6 | 166
9 | 166
Junior Supporter

Re: Finding next change in value

You could use my solution to fill a new table and then use what other databases call Dense Rank, look at the post by dnoeth for the Teradata work around:
http://forums.teradata.com/forum/database/dense-rank
Fan

Re: Finding next change in value

A couple of problems with that. I ran your solution and ran out of spool space. This is where you say "just increase your spool space". However as a user of the database, I have no control over the spool space nor do I have permissions to create a new table. I will take a look at dense-rank and see how I might use that. Any other suggestions would be appreciated.
Junior Contributor

Re: Finding next change in value

No need for a DENSE_RANK, you just need to look at the next 166 row after a 173 like:

select
...,
ts_col,
ts_col
- min(ts_col)
over (partition by ... order by ts_col rows between 1 following and 1 following) day(4) to second
from tab
where status in (166,173)
qualify status = 173

If there might be multiple 173 before the next 166:
...
ts_col
- min(case when status = 166 then ts_col end)
over (partition by ... order by ts_col rows between 1 following anf unbounded following) day(4) to second

Dieter

Re: Finding next change in value

HI all,

I have a data like below (SCD type -2)

emp_id amt_paid  start_dt  end_dt

------ ----  -------  --------

101 0  22-06-2015 23-06-2015

101 0  23-06-2015 24-06-2015

101 12  24-06-2015 25-06-2015

101 12  25-06-2015 26-06-2015

101 12  26-06-2015 27-06-2015

101 10  27-06-2015 28-06-2015

101 10  28-06-2015 29-06-2015

101 10  29-06-2015 30-06-2015

101 0  30-06-2015 01-07-2015

101 0  01-06-2015 02-07-2015

101 0  02-06-2015 03-07-2015

101 0  03-06-2015 04-07-2015

101 13  04-06-2015 05-07-2015

Expected Result we need is as below

emp_id amt_paid  start_dt  end_dt

------ -------- -------  --------

101 0   22-06-2015 24-06-2015

101 12  24-06-2015 27-06-2015

101 10  27-06-2015 30-06-2015

101 0  30-06-2015 04-07-2015

101 13  04-06-2015 05-07-2015

Any solution for this would be appreciated.

Thanks,

Vijay

Junior Contributor

Re: Finding next change in value

Hi Vijay,

you probably mean 07-2015 for the last for rows instead oof 06-2015.

What's your Teradata release?

TD14.10 has a nice new syntax:

SELECT emp_id, amt_paid, BEGIN(pd), END(pd)
FROM
(
SELECT NORMALIZE
emp_id, amt_paid, PERIOD(start_dt,end_dt) AS pd
FROM tab
) AS dt
ORDER BY 3,4

Before you need to utilize the TD_NORMALIZE_OVERLAP table function...