Archives of the TeradataForum

Message Posted: Wed, 30 Apr 2008 @ 15:35:14 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

I am having trouble coming up with a way to do the following, so any suggestions would be greatly appreciated.

Requirements:

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.

Thanks!

Megan

	<Prev	Next>		<<First	<Prev	Next>	Last>>