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

Database
Not applicable

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