Archives of the TeradataForum

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


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

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.*
        ,CASE
           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
         END AS HIGH_RANK_CHOICE

        FROM
           (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

        WHERE HIGH_RANK_CHOICE = 1
        ORDER BY 1;

Regards,

Johannes de Wet
Unum


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

Attachments

Library

Quick Reference