All Forums Database
Zoya 1 post Joined 06/15
20 Jun 2015
How to limit record to get max for only last three months if their record exist

Hi,
I have two tables on customer transaction. table1 provides all the transaction date wise and table2 provides maximun transaction value in a particular month. My query is if table1 has transaction date of 21/06/2015, then I need to compare transaction value against it  to Max_transaction_value for last 3 consecutive months which would be March, April and May and get the max of these three months                         
 
Sel account   ,customer ,transaction_value  ,transaction_date , Transacton_type
from table1
Join ( Sel account , month , Max_transaction_value
          from  xyz) table2
on table1.accont = table 2.account
 
Table1
Account  Customer   Transaction_value     Transaction_date      Transacton_type
A1             Vicky          3000                             29/01/2015             S
A1             Vicky          2000                             15/02/2015             C            
A1             Vicky          1500                             19/04/2015             S                       
A1             Vicky          500                               29/05/2015             T
A1             Vicky          1700                             21/06/2015             S
 
Table2
account  month   Max_transaction_value
A1           112014           700
A1           012015           7000 
A1           042015           1000  
A1           052015           2000  
     
Desired Output
Account  Customer   Transaction_value     Transaction_date      Transacton_type               Max_last_3 _months_trans
A1             Vicky          3000                             29/01/2015             S                                              700
A1             Vicky          2000                             15/02/2015             C                                              7000
A1             Vicky          1500                             19/04/2015             S                                               7000
A1             Vicky          500                               29/05/2015             T                                                1000
A1             Vicky          1700                             21/06/2015             S                                                2000
 
I do  not know how to limit max for last three months only for every record. Request you to help me on this.
 
Thanks,
Zoya
 
 

yuvaevergreen 93 posts Joined 07/09
25 Jun 2015
SEL
ACCOUNT1,
CUSTOMER,
TRANSACTION1,
TRANSACTION_dATE,
TRANSACTION_TYP,
MAX(max_trx)
FROM
(SEL  
ACCOUNT1,
CUSTOMER,
TRANSACTION1,
TRANSACTION_dATE,
TRUNC(TRANSACTION_dATE,'RM') AS TX_DATE1,
CAST(ADD_MONTHS(TRUNC(TRANSACTION_dATE,'MON'),-3) AS DATE FORMAT 'YYYYMM')  AS TX_DATE,
TRANSACTION_TYP,
max_trx,
MTH1
FROM T3,
(
SEL 
ACCOUNT2,
CAST(CAST('01'||mth AS DATE FORMAT 'DDMMYYYY') AS DATE FORMAT 'YYYYMM') AS MTH1,
max_trx 
FROM T41
) T41
WHERE T3.ACCOUNT1=T41.ACCOUNT2
AND T41.MTH1>=TX_DATE
AND T41.MTH1<TX_DATE1) A
GROUP BY 1,2,3,4,5

 

You must sign in to leave a comment.