Archives of the TeradataForum
Message Posted: Wed, 30 Apr 2008 @ 19:52:51 GMT
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!!
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|