Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-06-2017
03:26 AM

10-06-2017
03:26 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-06-2017
04:01 AM

10-06-2017
04:01 AM

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

www: http://www.ward-analytics.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-06-2017
04:21 AM

10-06-2017
04:21 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-06-2017
04:52 AM

10-06-2017
04:52 AM

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

www: http://www.ward-analytics.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-06-2017
05:14 AM

10-06-2017
05:14 AM

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

acc_num | Rate_Effective_Date | rate | prev_rate | rate_variance |

6589 | 05/01/2011 | 0 | 0 | 0 |

6589 | 06/01/2011 | 0 | 0 | 0 |

6589 | 08/01/2011 | 0 | -0.949999 | -2.45 |

6589 | 10/01/2011 | 0.449999 | 0 | 0 |

6589 | 11/01/2011 | 0.449999 | 0 | 0 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-06-2017
05:32 AM

10-06-2017
05:32 AM

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

www: http://www.ward-analytics.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-06-2017
05:40 AM

10-06-2017
05:40 AM

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

www: http://www.ward-analytics.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-06-2017
02:04 PM

10-06-2017
02:04 PM

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.