Archives of the TeradataForum

Message Posted: Wed, 30 Apr 2008 @ 17:51:02 GMT

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

Megan - How about something like the following? This should take care of not needing a QUALIFY statement and being able to select specific dates. I used the Ranking Descending and Ascending in the query below to help detect Customers with only one row, i.e. no 2nd place.

I wasn't sure though if you had a tool restriction with the WHERE as well - if you do, this may not work for you.

     SELECT
       CUSTOMER
      ,CUST_DATE
      ,CUST_AMT
      ,RANKVAL_DESC
      ,RANKVAL_ASC
     FROM
        (SELECT
        CUSTOMER
       ,CUST_DATE
       ,CUST_AMT
       ,ROW_NUMBER() OVER (PARTITION BY CUSTOMER
                                  ORDER BY CUST_AMT DESC) AS RANKVAL_DESC
       ,ROW_NUMBER() OVER (PARTITION BY CUSTOMER
                                  ORDER BY CUST_AMT ASC ) AS RANKVAL_ASC
    FROM
       MYCUST_TABLE A
    WHERE
       CUST_DATE GE '2008-03-01'
    ) A
    WHERE
       RANKVAL_DESC = 2
     OR
       (RANKVAL_ASC = 1 AND RANKVAL_DESC = 1);

Johannes de Wet
Unum

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