How to limit record to get max for only last three months if their record exist

Database
N/A

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

Tags (1)
1 REPLY
Enthusiast

Re: How to limit record to get max for only last three months if their record exist

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