Archives of the TeradataForum

Message Posted: Wed, 30 Apr 2008 @ 21:20:20 GMT


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

Subj:		Re: Retrieving the Second Highest Amount

From:		de Wet, Johannes M

Megan, Here's another idea. The query below pulls the 2nd largest (or 1st, if 2nd doesn't exist), Amount and Action Date combination based on the date range (GE '2008-03-01'). You'd still need to SUBSTR to get the Date and Amt separate.

     SELECT
     A.CUSTOMER
        ,CASE
            WHEN MAX(B.CUST_AMT) IS NULL
            THEN MAX(A.CUST_AMT||':'||A.CUST_DATE)
            ELSE MAX(B.CUST_AMT||':'||B.CUST_DATE)
         END AS MAX_2ND
     FROM
       MYCUST A
     LEFT JOIN
       MYCUST B
     ON
            A.CUSTOMER   = B.CUSTOMER
        AND B.CUST_DATE GE '2008-03-01'
        AND B.CUST_AMT   < A.CUST_AMT
     WHERE
        A.CUST_DATE     GE '2008-03-01'
     GROUP BY 1;

Johannes de Wet
Unum


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