All Forums Database
FredG 3 posts Joined 06/09
23 Aug 2011
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

teradatatester 69 posts Joined 01/10
23 Aug 2011

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

FredG 3 posts Joined 06/09
24 Aug 2011

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

teradatatester 69 posts Joined 01/10
24 Aug 2011

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

FredG 3 posts Joined 06/09
24 Aug 2011

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.

dnoeth 4628 posts Joined 11/04
24 Aug 2011

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

Dieter

vijayoec2009 1 post Joined 06/15
21 Jun 2015

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

dnoeth 4628 posts Joined 11/04
23 Jun 2015

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...

Dieter

You must sign in to leave a comment.