All Forums Database
sam141988 7 posts Joined 08/16
10 Aug 2016
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.

AtardecerR0j0 71 posts Joined 09/12
11 Aug 2016
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

 

Be More!!

sam141988 7 posts Joined 08/16
11 Aug 2016

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

AtardecerR0j0 71 posts Joined 09/12
11 Aug 2016

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

 

Be More!!

sam141988 7 posts Joined 08/16
11 Aug 2016

Thanks Alot!

You must sign in to leave a comment.