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
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;
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.
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?
simply looking for any change between the rate rows. This is what I am currenly getting back:
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?
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.
,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.