Capturing Versions when there is a Change in a Column Value else keep the Earliest if Consecutive

Database
Enthusiast

Capturing Versions when there is a Change in a Column Value else keep the Earliest if Consecutive

Hi Experts Can you All please suggest how this can be achived in TD-

Consider the Sample Source Data -

ID|START_DATE|VALUE

110333005|2000-05-03 00:00:00.000000|0.00

110333005|2014-01-08 12:38:40.000000|0.00

110333005|2014-01-10 20:26:58.000000|103.14

110333005|2014-01-22 07:50:20.000000|0.00

110333005|2014-01-28 23:19:42.000000|0.00

110333005|2014-02-10 15:49:52.000000|110.87

110333005|2014-02-25 06:45:08.000000|0.00

110333005|2014-03-10 14:34:57.000000|103.14

Now I want to Retain the Most Top Value within a Consecutive Group, I mean to say when similar Data in "Value" comes consecutively it must retain the Oldest and also i need t consider when the Same value comes after some otehr value it should be inserted and not ignored.

The Output i am looking for is :

ID|START_DATE|VALUE

110333005|2000-05-03 00:00:00.000000|0.00

110333005|2014-01-10 20:26:58.000000|103.14

110333005|2014-01-22 07:50:20.000000|0.00

110333005|2014-02-10 15:49:52.000000|110.87

110333005|2014-02-25 06:45:08.000000|0.00

110333005|2014-03-10 14:34:57.000000|103.14

Suggest How this can be achieved.

4 REPLIES
Teradata Employee

Re: Capturing Versions when there is a Change in a Column Value else keep the Earliest if Consecutive

drop table table1;
create multiset volatile table table1
(
id integer,
start_date timestamp,
valor decimal (5,2)
)primary index( id )
on commit preserve rows;

insert into table1 values(110333005,'2000-05-03 00:00:00.000000', 0.00);
insert into table1 values(110333005,'2014-01-08 12:38:40.000000',0.00);
insert into table1 values(110333005,'2014-01-10 20:26:58.000000',103.14);
insert into table1 values(110333005,'2014-01-22 07:50:20.000000',0.00);
insert into table1 values(110333005,'2014-01-28 23:19:42.000000',0.00);
insert into table1 values(110333005,'2014-02-10 15:49:52.000000',110.87);
insert into table1 values(110333005,'2014-02-25 06:45:08.000000',0.00);
insert into table1 values(110333005,'2014-03-10 14:34:57.000000',103.14);

insert into table1 values(110333007,'2000-05-03 00:00:00.000000', 0.00);
insert into table1 values(110333007,'2014-01-08 12:38:40.000000',0.00);
insert into table1 values(110333007,'2014-01-10 20:26:58.000000',103.14);
insert into table1 values(110333007,'2014-01-22 07:50:20.000000',0.00);
insert into table1 values(110333007,'2014-01-28 23:19:42.000000',0.00);
insert into table1 values(110333007,'2014-02-10 15:49:52.000000',110.87);
insert into table1 values(110333007,'2014-02-25 06:45:08.000000',0.00);
insert into table1 values(110333007,'2014-03-10 14:34:57.000000',103.14);

select
ID,START_DATE,valor
from table1
qualify row_number() over (partition by ID
order by start_date
reset when valor <> min(valor) over( partition by ID
order by start_date
rows between 1 preceding and 1 preceding ) )=1
Enthusiast

Re: Capturing Versions when there is a Change in a Column Value else keep the Earliest if Consecutive

Hi @AtardecerR0j0 

Thank You So Much for the Solution this is Exactly what i was looking for ! 

I am New to Teradata can you please explain me once how it is working in Steps?

Thanks in Advance

Teradata Employee

Re: Capturing Versions when there is a Change in a Column Value else keep the Earliest if Consecutive

Very important: Read the documentation to understand Row_Number and min functions

http://www.info.teradata.com/htmlpubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1145_151K/ROW_...

And this query can help you to understand step by step:

select 
ID,START_DATE,valor ,
row_number() over (partition by ID
order by start_date) as Row_Number1,
min(valor) over( partition by ID
order by start_date
rows between 1 preceding and 1 preceding ) as Min1,
row_number() over (partition by ID
order by start_date
reset when valor <> min(valor) over( partition by ID
order by start_date
rows between 1 preceding and 1 preceding ) )
from table1
Enthusiast

Re: Capturing Versions when there is a Change in a Column Value else keep the Earliest if Consecutive

Thanks Alot!