Home Page for the TeradataForum
 

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016