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

2 weeks ago

2 weeks ago

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

2 weeks ago

2 weeks ago

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

2 weeks ago

2 weeks ago

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

2 weeks ago

2 weeks ago

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

2 weeks ago

2 weeks ago

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

2 weeks ago

2 weeks ago

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

2 weeks ago

2 weeks ago

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

2 weeks ago

2 weeks ago

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.