Archives of the TeradataForum

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

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

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