SQL Aggregation logic - Not sure

Database
Enthusiast

SQL Aggregation logic - Not sure

Greetings Experts,

I am implementing the multiple separate logics between 2 tables by joining them in a view.  I need to have minimal number of views with all logics implemented.  I am struck with the following issue while implementing and need your expertise.

I had arrived to the base logic of the view as it serves as the base for most of the logics and I am to stick to it;

SELECT Acct_no, max(txn_date),......
FROM ACCT_CRD ac INNER JOIN TRNSCTN txn ON
( ac.crd_no = txn.crd_no)
GROUP BY ACCT_NO, TO_CHAR(TXN_DATE,'YYYYMM');

Table_name: ACCT_CRD (This table has account and the credit card numbers with UPI on credit card numbers and a single account number can have multiple card_numbers)

Data:

Acct_no Crd_no biz_date Status
acct1 crd11 2015-10-01 A
acct1 crd12 2015-10-02 A
acct1 crd13 2015-10-03 A
acct2 crd21 2015-10-01 A
acct2 crd22 2015-10-02 A

Table_name: TRNSCTN (This table has transactions done through the credit cards; data doesn't reflect any actual meaning)

Data:

Crd_no   Txn_date Txn_code crd_limit crd_commit
crd11 2015-10-02 10 10000 9000
crd11 2015-10-02 10 10000 14000
crd11 2015-10-02 20 10000 16000
crd11 2015-10-03 20 10000 12000
crd11 2015-10-05 20 10000 15000
crd12 2015-10-03 10 20000 5000
crd12 2015-10-03 20 20000 22000
crd12 2015-10-04 30 20000 25000
crd12 2015-10-04 30 20000 5000
crd13 2015-10-04 30 25000 10000
crd21 2015-10-02 10 20000 8000
crd21 2015-10-02 10 20000 27000
crd21 2015-10-03 10 20000 1000

Here, in TRNSCTN table, for each card on each day if CRD_COMMIT > CRD_LIMIT, then take the count as 1 even if there are more records with same card_no and txn_date with crd_commit >= crd_limit or crd_commit < crd_limit;

Order is not important on a given day transactions;

select crd_no, txn_date, max(case when crd_commit > crd_limit then 1 else 0 end) day_overlimit_cnt from TRNSCTN 
group by crd_no, txn_date;

Essentially, the above data in the table transforms to

Crd_no txn_date day_overlimit_cnt
crd11 2015-10-02 1
crd11 2015-10-03 1
crd11 2015-10-05 1
crd12 2015-10-03 1
crd12 2015-10-04 1
crd13 2015-10-04 0
crd21 2015-10-02 1
crd21 2015-10-03 0

Then, I need to find for each card in a given month, how many times it has exceeded the day_overlimit_cnt;

select crd_no, to_char(txn_date,'yyymm') as txn_mnth, SUM(day_overlimit_cnt) sum_month_ovrlmt from (
select crd_no, txn_date, max(case when crd_commit > crd_limit then 1 else 0 end) day_overlimit_cnt from TRNSCTN
group by crd_no, txn_date) dt_check
group by crd_no, to_char(txn_date,'yyymm');

The data from the above query will be

Crd_no txn_mnth  sum_month_ovrlmt
crd11 201510 3
crd12 201510 2
crd13 201510 0
crd21 201510 1

And then finally find the max(sum_month_ovrlmt) at account level by joining the above one to ACCT_CRD;

select acct_no, max(sum_month_ovrlmt) acct_mnth_ovrlmt from ACCT_CRD ac join
(select crd_no, to_char(txn_date,'yyymm') as txn_mnth, SUM(day_overlimit_cnt) sum_month_ovrlmt from (
select crd_no, txn_date, max(case when crd_commit > crd_limit then 1 else 0 end) day_overlimit_cnt from TRNSCTN
group by crd_no, txn_date) dt_check
group by crd_no, to_char(txn_date,'yyymm')) dt_dt_check dt
on (ac.cr_no = dt.crd_no)
group by acct_no;

Final output:


Acct_no  acct_mnth_ovrlmt
acct1 3
acct2 1

How to embed the above logic into the following base query;  That is how to derive acct_mnth_ovrlmt without affecting the other columns data in the select part.

SELECT Acct_no, max(txn_date),......
FROM ACCT_CRD ac INNER JOIN TRNSCTN txn ON
( ac.crd_no = txn.crd_no)
GROUP BY ACCT_NO, TO_CHAR(TXN_DATE,'YYYYMM');

Thanks in advance for your time.





Tags (1)