Archives of the TeradataForum

Message Posted: Thu, 23 Jul 2015 @ 07:17:23 GMT

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

Good Morning,

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:

     SELECT
         TX.Clnt_Intrnl_id
     ,   TX.Tx_Id
     ,   TX.Clnt_Acnt_id
     ,   TX.Rl_typ_cd
     ,   TX.ACNT_PERD_BG_DT
     ,   TX.ACNT_PERD_END_DT

     .......
     FROM    .....

     JOIN....

     JOIN ....

     WHERE   .......

     QUALIFY ROW_NUMBER () OVER
     (PARTITION BY   TX.Clnt_Intrnl_id,   TX.Clnt_Acnt_id,   TX.Rl_typ_cd,
     TX.ACNT_PERD_END_DT
           ORDER BY
             CASE
               WHEN   TX.Tx_Prcs_Dt >  Pop.IA_WI_Abslt_Strt_Dt
                 AND   Pop.IA_WI_Typ_Nm = 'Service Request'
                 AND   TX.Tx_Prcs_Dt BETWEEN  (POP.IA_WI_Cmpltd_Dt  - INTERVAL
     '14' DAY)   AND (POP.IA_WI_Cmpltd_Dt + INTERVAL '28' DAY)
                 AND   TX.Asmt_Typ_cd = 3
                 AND lur.Form_LU_Rsn_cd  IN (5,  712)
               THEN    CAST(CAST(CAST(Fincl_Tx_LU_Tmstmp AS TIMESTAMP(6) FORMAT
     'yyyymmddhhmiss')AS CHAR(14))AS DECIMAL(18,0)) *-1 --needs to be ascending order

               WHEN   TX.Tx_Prcs_Dt >  Pop.IA_WI_Abslt_Strt_Dt
                 AND   Pop.IA_WI_Typ_Nm = 'Service Request'
                 AND   TX.Tx_Prcs_Dt > Pop.IA_WI_Abslt_Strt_Dt
                 AND   TX.Tx_Prcs_Dt BETWEEN (POP.IA_WI_Cmpltd_Dt  - INTERVAL
     '14' DAY)   AND (POP.IA_WI_Cmpltd_Dt + INTERVAL '60' DAY)
              THEN   CAST(CAST(CAST(Fincl_Tx_LU_Tmstmp AS TIMESTAMP(6) FORMAT
     'yyyymmddhhmiss')AS CHAR(14))AS DECIMAL(18,0))

              WHEN   TX.Tx_Prcs_Dt >  Pop.IA_WI_Abslt_Strt_Dt
                AND    Pop.IA_WI_Typ_Nm = 'Case'
                AND   TX.Tx_Prcs_Dt > Pop.IA_WI_Abslt_Strt_Dt
                AND   TX.Tx_Prcs_Dt BETWEEN (POP.IA_WI_Cmpltd_Dt  - INTERVAL '14'
     DAY)   AND (POP.IA_WI_Cmpltd_Dt + INTERVAL '60' DAY)
             THEN   CAST(CAST(CAST(Fincl_Tx_LU_Tmstmp AS TIMESTAMP(6) FORMAT
     'yyyymmddhhmiss')AS CHAR(14))AS DECIMAL(18,0))
           END    DESC
      ) = 1
     ;

What I'd like to know, is are there other more transparent, elegant, efficient ways to achieve the same thing?

Thanks in advance.

Steve Saunders.

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