Archives of the TeradataForum

Message Posted: Wed, 30 Apr 2008 @ 19:52:51 GMT

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

Hi,

Thanks for this ... I am going to give it a try and see if I can get it to work. The biggest issues are that the view has to be predefined, the dates are floating, and the actual 'AMOUNT' has to come through in the Select part of the SQL statement.

Unfortunately, I cannot pass in the dates as parameters either, through macros, due to the way the software is set up.

If I set up my view to be like the following then I get their 2nd best (or first if there is no second row) over all time.

     Replace View ...
     Select
        Customer,
        Action_Date,
        Amount
     from sql1.table1
     qualify rank() over (partition by Customer order by Amount desc) =
          case when count(*) over (partition by Customer) > 1 then 2 else 1
     end
     ;

When I run the Select based on that view for the specific dates (ie >= '2008-03-01') then most people are dropped from the results because their 1st or 2nd highest amounts have not happened since March.

I need that ranking to happen at the time I run the select on the view and only for the dates that the Select has asked for.

Thanks to all of you for your suggestions so far!!

Megan

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