9.3 How to Calculate the CIB Parameter
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.