Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Fri, 06 Jul 2007 @ 16:32:15 GMT


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


Subj:   Re: Total payment volume per transaction per customer
 
From:   Kopuuravuri, Ramu

Hi

Seems u need detail data with aggregate values.

     i.e.
     T1

     Txn_id  Txn_date    Txn_amount_$  User_id
     001     06/27/2007  3.00          1010
     002     06/28/2007  5.00          1010
     003     06/05/2007  8.00          1010

Report like :

     Txn_id  User_id  date        TPV_30days
     001     1010     06/27/2007  450.00
     002     1010     06/28/2007  450.00
     003     1010     06/05/2007  450.00

If this is ur requirement then first u need to populate all base values in to target table (i.e. with out derived field.)

     Txn_id  User_id  date        TPV_30days
     001     1010     06/27/2007  ?
     002     1010     06/28/2007  ?
     003     1010     06/05/2007  ?

Then update the above target table using

     update tgt_table
     from
     (sel
     Txn_id id,
     User_id usr_id,
     sum(case when date between max(date) and max(date)-30 /*case when date
     between current_date and current_date-30*/
        then Txn_amount_$
        else 0
        end) TPV_30days_amt
     from T1
     group by 1,2) stg_table

     set TPV_30days = stg_table.TPV_30days_amt
     where
        Txn_id = stg_table.id
     and User_id = stg_table.usr_id

If this is not ur requirement, then let me know the sample with multiple rows and report format.


Regards,

Ramu.



     
  <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: 28 Jun 2020