Topics:
· How to Calculate the Target Amount
· How to Calculate the CIB Parameter
The following query is a suggested way of arriving at these target amounts:
select t.JRSDCN_CD,(t.AVG_AMT + 2*t.SD) as min_amt,(t.AVG_AMT + 5* t.SD) as max_amt
from
/*Consider mean of deposit and withdrawal amounts rather than just one or the other. */
(select JRSDCN_CD, AVG((TOT_DEPST_AM+TOT_WDRWL_AMT)/2) as avg_amt, STDDEV((TOT_DEPST_AM+TOT_WDRWL_AMT)/2) as sd
from CUST a
inner join
CUST_SMRY_MNTH b
on a.CUST_INTRL_ID = b.CUST_INTRL_ID
/*Choose an appropriate time frame */
where MNTH_SMRY_START_DT between '01-MAY-15' and '01-SEP-15'
/* This assumes JURSDCN_CD = Segment. if not adjust appropriately */
group by JRSDCN_CD) t;
The following query is a suggested way for calculating CIB parameters from the data:
/*CIB Parameters*/
select c.JRSDCN_CD,
AVG(t.AVG_CREDIT_AMT) as AVG_CREDIT_AMT,
AVG(t.AVG_DEBIT_AMT) as AVG_DEBIT_AMT,
AVG(t.SD_CREDIT) as SD_CREDIT,
AVG(t.SD_DEBIT) as SD_DEBIT,
AVG(t.MAX_CREDIT_AMT) as MAX_CREDIT_AMT,
AVG(t.MAX_DEBIT_AMT) as MAX_DEBIT_AMT
from acct a INNER JOIN
cust_acct ca
on a.ACCT_INTRL_ID = ca.ACCT_INTRL_ID
inner join cust c
on ca.CUST_INTRL_ID = c.CUST_INTRL_ID
inner join
(select ACCT_INTRL_ID,
AVG(TOT_DEPST_AM) as AVG_CREDIT_AMT,
AVG(TOT_WDRWL_AM) as AVG_DEBIT_AMT,
STDDEV(TOT_DEPST_AM) as SD_CREDIT,
STDDEV(TOT_WDRWL_AM) as SD_DEBIT,
MAX(TOT_DEPST_AM) as MAX_CREDIT_AMT,
MAX(TOT_WDRWL_AM) as MAX_DEBIT_AMT
from acct_smry_mnth
--where MNTH_SMRY_START_DT between '01-MAY-15' and '01-SEP-15'
where MNTH_SMRY_START_DT >= add_months(trunc(sysdate, 'month'), - 12) -- Recent 12 months data
and MNTH_SMRY_START_DT < trunc(sysdate, 'month')
GROUP BY ACCT_INTRL_ID) t
on a.ACCT_INTRL_ID = t.ACCT_INTRL_ID
GROUP BY c.JRSDCN_CD