Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 18 Aug 2015 @ 19:29:06 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: How to Order by using Case statements
From:   de Wet, Johannes M

Stephen Saunders wrote (July 22,2015):

  I'm looking for additional options in relation to some code I'm currently building...  

  My brief is to create an output table that contains (amongst other data) a list of transactions pertaining to clients.  

  The challenge is to only return the "highest ranking "transaction per client, per period, where multiple transactions can occur in a single period, for the same client - the ranking is determined by a Timestamp, that represents when the transaction was committed to the record.  

  It gets a little messy, as the conditions that determine the "highest ranking " transaction change, depending on a number of conditions...i.e. in some circumstances, we want the earliest Transaction, while in other circumstances we want the latest.  

  Currently, I have 2 options:  

  1. Create 2 separate insert statements - each with the population and sort order defined by the specific conditions. Each of these will need to explicitly exclude the population of the other, as there may be some "crossover"  

  2. Create a single statement, that ranks and orders the data, using a Case statement in the Order By clause.  

  Although 1. is probably the "simplest", in relation to coding, the table builds already exist, and we are looking at changing 8 separate tables...probably a little time consuming...  

  The complexity with 2., is that the order by includes both an ascending sort and a descending sort, depending on the conditions.  

  I've come up with the code below, whereby I cast the Timestamp to a number, and convert 1 element to a negative, so I can apply a single Descending Sort  

How about something like this Steve? The query illustrates how you could choose the first of the month if the month is an uneven month, and choose the end of the month of if it is even.

     SELECT AA.*
           WHEN AA.MONTH_OF_YEAR IN (1,3,5,7,9,11) THEN AA.ASC_RULE
           WHEN AA.MONTH_OF_YEAR IN (2,4,6,8,10)   THEN AA.DESC_RULE

           (SELECT  a.*
                 , ROW_NUMBER() OVER (PARTITION BY A.Month_of_year ORDER BY day_of_month ASC)  AS ASC_RULE
                 , ROW_NUMBER() OVER (PARTITION BY A.Month_of_year ORDER BY day_of_month DESC) AS DESC_RULE
              FROM sys_calendar.CALENDAR A
             WHERE YEAR_OF_CALENDAR = '2015') aa

        ORDER BY 1;


Johannes de Wet

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 24 Jul 2020