Archives of the TeradataForum
Message Posted: Thu, 07 Mar 2013 @ 21:28:17 GMT
For a 3 month sliding window, you can do the following on the windowing function providing you have set number of rows in the window (i.e. monthly statement amounts that you can count on being static). If you know you will always have 3 values in the list to select the median just qualify the result to be the second value in the partition.
ROWS BETWEEN 2 PROCEEDING and CURRENT ROW
Otherwise you can use the calendar table and derive the quarter_of_calendar value and add that to the partitioning (i.e. account_number, quarter_of_calendar). The median average (middle value) will still be applicable no matter the row volume - as long as you can quantify it.
Kyle Prescott | DBA Manager | Unum-GHDS | Chattanooga, TN
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|