Archives of the TeradataForum
Message Posted: Thu, 01 May 2008 @ 09:23:51 GMT
I am far from the database at the moment and could not try any examples myself but from what I have understood from the discussion so far the above behavior is correct. If the WHEN clause is inside the view definition then it is evaluated first and the ranking is done over the period of time specified by the WHEN clause. If the WHEN goes outside of the view, then the view will have to do the ranking globally (over the entire period of five years) and only after that the external WHEN will apply, which may result in a totally different answer set: some customers may have a few orders (let's assume we are talking about orders here) after March 1 but if they used to place larger orders before March 1 then the global #2 ranking would be dated prior to March 1 (that's in the result of the "global" view) and the subsequent external WHEN (date >= March 1) will eliminate such customers.
I'd also mention one hypothetical view-based solution. Not sure it is a practical one. If the number of customers is not too high it could be considered.
Let's assume that the periods are of type "date >= start_date".
The ranking the OP is talking about can change any day so one could consider building a view which would provide the answer for each customer and ***each start_date*** from 2003 to, say, 2020 (or whatever date in the future).
I am not going to write SQL without a database to test it on but the idea is to join the OP's table to the (system) calendar table and calculate the ranking for each calendar date as start_date. Hopefully, this can be realized as no dynamic parameters need to be submitted to the view. The downside is obvious. The view would generate (too) many rows per customer but I have a feeling that this is the only possible view-based solution of this kind.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|