Archives of the TeradataForum
Message Posted: Wed, 30 Apr 2008 @ 15:35:14 GMT
I am having trouble coming up with a way to do the following, so any suggestions would be greatly appreciated.
1 - I have detail records for all customers for 5 years.
2 - I need to be able to pull out the SECOND highest amount for each customer over a variable time interval
3 - If there is no second row for that customer over the time period, I need to pull the highest amount for him.
4 - If this is done in the select statement, I cannot have a Qualify statement included in the SQL ... it has to be done completely in the Select. This is a restriction of the software that I am trying to write it for. However, if there is a way to do this in a view - allowing for the floating time intervals - then there are no restrictions on how it is written.
Below is sample data. In this case, the time period I have chosen is for all activity for all customers where the date is >= '2008-03-01' and the data is grouped by customer and ordered by amount descending.
I would like to be able to return the amounts for the rows marked with an asterisk.
CUSTOMER DATE AMOUNT ------------- --------- ----------- 1000020 3/30/2008 368.622 *1000020 3/15/2008 93.109 1000020 3/9/2008 1.331 *1000082 3/17/2008 38.322 1000098 3/1/2008 14.114 *1000098 3/15/2008 13.802 1000099 3/15/2008 2.396
What I have tried:
--Adding either the RANK() or ROW_NUMBER() in the base view to order the rows according to player and amount descending
-- This ranks ALL of the rows in the detail table whether or not they are from March 1 or not. Therefore, I cannot pull out those with only the Rank or Row Number = 1 or 2.
-- Using the ROW_NUMBER() function in the select clause with a Case Statement
-- This does rank the rows correctly, but then I cannot do a MAX or MIN on an ordered analytical function to find the correct amount.
If you have any suggestions, please let me know.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|