All Forums Database
Vga 12 posts Joined 02/12
10 Sep 2012
Get the previous row's column Value

Hello All,

I have got source data in following way:

Col_1 | Col_2

100 | 2000

100 | 3000

100 | 4000

300 | 5000

300 | 6000

300 | 7000

I need to generate an extra column as Col_3 (based upon Col_2) where it contains the value from previos row, starting from 0 for  every group based on Col_1. The outcome expected as:

 

 

Col_1 | Col_2 | Col_3

100 | 2000 | 0

100 | 3000 | 2000

100 | 4000 | 3000

300 | 5000 | 0

300 | 6000 | 5000

300 | 7000 | 6000

Help !!!

dnoeth 4628 posts Joined 11/04
10 Sep 2012
select
  coalesce(min(col_2) over (partition by col_1  order by col_2 rows between 1 preceding and 1 preceding), 0)

Dieter

Dieter

Sankalp.C 45 posts Joined 08/13
15 Dec 2013

Awesome! dieter

vchadala 2 posts Joined 07/14
24 Jul 2014

Can somebody tell for next rows syntax? SUCCEEDING ??

Raja_KT 1246 posts Joined 07/09
24 Jul 2014

I thibk this is what you are looking for.
rows between 1 following and 1 following 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

georgesun 1 post Joined 09/14
24 Sep 2014

Dears,
Can some one help please .I arrive at a wrong result in CALL_SEQUENCE at the step highlighted in yellow

EVT_DT |CLE|DELTA|CALL_SEQUENCE|CORRECT_CALL_SEQUENCE
19/11/2013 12:37| 10222014|  -1| 1| 1
22/11/2013 19:00| 10222014|   3| 2| 2
22/11/2013 20:01| 10222014|   0| 3| 3
25/11/2013 18:51| 10222014|   3| 4| 4
05/12/2013 17:47| 10222014|  10| 1| 1
05/12/2013 18:37| 10222014|   0| 6| 2
07/12/2013 17:44| 10222014|   2| 7| 3
16/07/2014 20:18| 10222014| 221| 1| 1
16/07/2014 20:25| 10222014|   0| 9| 2

Formula to calculate call_sequence:

ORDER BY CLE,EVT_DT
WHEN DELTA=-1 OR DELTA >3 THEN 1 ELSE ADD 1 TO PRECEDING CALL_SEQUENCE

My code :There's an issue because 1 is being added to the row_number when I actually want it to be added to previos call_sequence 

SEL EVT_DT 
 ,CLE        
,DELTA
,CASE WHEN  DELTA=-1 OR DELTA>3  THEN 1 ELSE
ROW_NUMBER() OVER ( PARTITION BY  CLE   ORDER BY CLE,EVT_DT  ASC ) END AS CALL_SEQUENCE
FROM (
 SEL EVT_DT 
,CLE        
,CASE WHEN CLE=MAX(CLE)  OVER (ORDER BY CLE,EVT_DT  ASC   ROWS BETWEEN  
1 PRECEDING   AND 1 PRECEDING)   
THEN CAST(EVT_DT AS DATE )-MAX(CAST(EVT_DT AS DATE ))  OVER ( ORDER BY CLE,EVT_DT  ASC   ROWS 
BETWEEN  1 PRECEDING   AND 1 PRECEDING)  ELSE -1 END AS DELTA 
 FROM  TABLE_NM  ADV
  WHERE CLE IN ('10222013','10222014','10222015')  ORDER BY CLE,EVT_DT

 

 

CHETS 4 posts Joined 06/15
22 Jun 2015

Hi All , 
I need help for the below scenario.

SOURCE DATA >>>> (S1,S2,S3...S300)

 

 TABLE DATA

 

 SID, UID 

 S1, U1

 S2 , U5

 S3,  U1

 S1, U7

 S3,U7

 S7,U1

 .....

  NOW OUTPUT OF THIS DATA SHOULD BE LIKE 

 

CONDITION IF SOURCE DATA COMING ONLY FOR SID (S1 ,S3)

IT SHOULD DISPLAY THE UID HAVING BOTH OF THESE VALUES .

EXAMPLE THE OUTPUT SHOULD COME AS 

UID >>> U1  AND U7

 

SO THE SITUATION IS LIKE , IT MAY HAPPEN THAT SOURCE DATA CAN COME AS ONE 'SID' VALUE  

OR MORE THAN ONE 'SID' VALUES .

IT IS DYNAMIC

THE SOURCE DATA WILL COME ALWAYS (S1,S2,....) FORMAT ONLY WE HAVE TO SEPERATE EACH SID VALUE .

 

SO , HOW CAN WE ACHIVE THIS USING ONLY  'TERADATA SQL'

dnoeth 4628 posts Joined 11/04
23 Jun 2015

This will return the UID if both values exist:

select uid
from tab
where SID in ('S1','S2')   -- only searched values
group by uid
having min(SID) <> max(SID)-- both must exist

If you need to search for more than two values change to:

having count(distinct SID) = 2 -- number of searched values

If there are no duplicate values you can remove the DISTINCT.
 
But what if there's an additional value like 'S3', the previous will still return the UID.
If you need exactly 'S1' and 'S2' but no other value:

select uid
from tab
group by uid
having
  count(distinct case when SID in ('S1','S2') then SID end) = 2 -- both values
and 
  count(case when SID in ('S1','S2') then NULL else SID end) = 0 -- but no other value

Again, if there are no duplicate values you can remove the DISTINCT

Dieter

CHETS 4 posts Joined 06/15
24 Jun 2015

Thanks Dieter for you Instant Reply,
But in my case  the values or the count is not fixed . It may happen that we can get two values or Twelve values .
In such case where we dont know how many Source ID (SID) will come , how can we handle it ?

CHETS 4 posts Joined 06/15
24 Jun 2015

WITH YOU Query Dieter ! when we use the IN operator it will also give those user names(UID) which are having only permission like (S1).
SID is permission like Grant , Read , Execute , Write so on  S1......S300
UId are the usernames U1....Un
so one UID can have many Permissions or vice versa.
Now suppose our Client needs  to know how many users  are having the access of Read(s7) and Grant(s12) 
So it should display the userid , having both the Permissions.But this permission values is not fixed , means any number of Permissions can come as Input ranging (S1 to S300) so we cannot write a fixed values query to acheive it .
APart from this , the value will be like (S1,S8,S9)  then we need to seperate each of the Permission and find the required User id .
I hope m clear enough this time :P .

Vivek7m 1 post Joined 02/12
05 Jul 2015

Hi Dieter,
 
On your reply with the query:
select  coalesce(min(col_2) over (partition by col_1  order by col_2 rows between 1 preceding and 1 preceding), 0)
Would I get Column not found error for this query due to TD version issues? I can see the same columns in the table, but when I use the columns in this query it says column not present.
 
 

dnoeth 4628 posts Joined 11/04
05 Jul 2015

This syntax is the same since V2R4.
Double check if you did somethng else wrong.

Dieter

You must sign in to leave a comment.