Difference between 2 row values, in the same field

Database
Enthusiast

Difference between 2 row values, in the same field

This is a sample of my data.

I want to get the difference/change (if any) between each RATE Rows, on the RATE_DATE for each ACC_NUM. I want the result as part of my Select statement amongst other existing fields. How do I code this new field, that I will name as "RATE_VARIANCE"??

 

acc_num    Rate_Date   Rate           RATE_VARIANCE
6589           01/06/2008 1.25   
6589           03/04/2009 1.65   
6589           30/04/2009 1.50   
6589           05/06/2009 1.45  
5711           20/03/2009 2.45  
5711           03/04/2009 1.25   
5711           30/04/2009 1.25   
5711           05/06/2009 1.95  
5711           18/04/2011 1.25  

7 REPLIES
Senior Apprentice

Re: Difference between 2 row values, in the same field

Hi,

I assume that when you say "between each RATE Rows, on the RATE_DATE ", you mean the difference between the RATE for a row and the RATE for the same ACC_NUM for the previous date?

 

Using your example data above, the 'rate variance' for acc_num = 6589 for date = 30/4/2009 would be -0.15 (1.50 - 1.65).

 

If so, try the following (table VT1 is my testing table):

SELECT a.*
  ,MAX(rate) OVER(PARTITION BY acc_num ORDER BY rate_date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_rate
  ,rate - prev_rate AS rate_variance  
FROM vt1 AS a;

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Difference between 2 row values, in the same field

Why use MAX?

I want each RATE row compared with each preceeding row, not just the Max RATE for that account.

 

Using your suggestion, I am getting varying results - mostly just giving the Max or Min variance between the account (RATE) values. Or Null values.

 

Thanks..

Senior Apprentice

Re: Difference between 2 row values, in the same field

Hi,

 

It doesn't matter whether you use Max or Min (my two preferred methods). You have to use an aggregate function because this syntax uses 'windowed aggregate' functions. Please see https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1145_151K/Ordered... for details.

 

It won't change the results because the ROWS clause limits that set of data to 1 row which only contains a single value for a single column.

 

What you're getting is the difference between the RATE value on one row and the RATE value on the preceding row, sorted in rate_date order for each acc_num value.

 

If that isn't what you want, can you please provide a sample of the expected result values? Given your earlier sample data and my sample code I get the following results:

acc_num	rate_date	rate	prev_rate	rate_variance
5711	20/03/2009	2.45	?	        ?
5711	03/04/2009	1.25	2.45	        -1.20
5711	18/04/2009	1.25	1.25	        0.00
5711	30/04/2009	1.25	1.25	        0.00
5711	05/06/2009	1.95	1.25	        0.70
6589	01/06/2008	1.25	?	        ?
6589	03/04/2009	1.65	1.25	        0.40
6589	30/04/2009	1.50	1.65	        -0.15
6589	05/06/2009	1.45	1.50	        -0.05

For this sample data, what values do you want in column rate_variance on each row?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Difference between 2 row values, in the same field

simply looking for any change between the rate rows. This is what I am currenly getting back:

 

acc_numRate_Effective_Daterateprev_raterate_variance
658905/01/2011000
658906/01/2011000
658908/01/20110-0.949999-2.45
658910/01/20110.44999900
658911/01/20110.44999900
Senior Apprentice

Re: Difference between 2 row values, in the same field

Hi,

Using your latest set of data I get different results to you.

 

acc_num	rate_date	rate	        prev_rate	rate_variance
6589	05/01/2011	0.00	        ?	        ?
6589	06/01/2011	0.00	        0.00	        0.00
6589	08/01/2011	0.00	        0.00	        0.00
6589	10/01/2011	0.449999	0.00	        0.449999
6589	11/01/2011	0.449999	0.449999	0.00

This is calculated as:

- sort the rows for each acc_num on rate_date (ascending)

- work through each row and calculate: rate (on 'this' row) - rate (on 'previous' row). The result is rate_variance.

 

So for the row where acc_num = 6589 and rate_date = 10/01/2011:

- rate (on 'this' row) = 0.44999

- rate (on 'previous' row - dated 08/01/2011) = 0.00

The difference between those two values is 0.44999 - which is the value for rate_variance on 'this' row.

 

As far as I can tell my answers are correct.

 

A couple of things.

1) What version of Teradata are you using?

2) This is important. You've said what results you are getting, but what results do you want? What values are you expecting for column rate_variance on each row?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: Difference between 2 row values, in the same field

Just a thought... What format are your dates shown above?

 

Are they DD/MM/YYYY or MM/DD/YYYY?

 

The logic doesn't change but it might affect the results.

 

It would still be useful to if you could provide some expected answers as well.

 

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Difference between 2 row values, in the same field


SELECT

Tbl.*
,SUM(Rate) OVER(ORDER BY Rate_Date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS Cummulative_Sum
,(Cummulative_Sum- Rate) AS Prev_Row
,(Rate - Prev_Row)  AS Variance
FROM <someTable> Tbl;

 

This assumes that there are no duplicates and sort order is date.