oracle-sql-Monthly statistics on self-service terminal volume

The goal of the SQL implementation:

Basic situation							Cash trading situation				Transfer status		Transfer Transaction status (details)								Other business situation			Volume summary	Daily Trading Volume	Transaction amount	Performance situation (million yuan)
Branch Name	Branch No.	Affiliated Network	Network number	Administrator account	Administrator	Terminal number	Number of withdrawal pens	Withdrawal Amount	Number of deposit Pens	Deposit Amount	Number of money transfers	Transfer amount	Card Pen Number	Card Amount	Number of card folds	Card Fold Amount	Number of folding cards	Discount Card Amount	Number of folding Pens	Fold amount	Number of payment on behalf of the pen	Amount of payment on behalf of	Query number of Pens				Deposit balance (monthly average)	Balance of deposits (monthly average daily than last month)
Banan Branch	10	101302	East Spring Sub-branches	402230080416813378	Luo Chung	23003326	127	151950	51	43300	7	13562	0	0	0	0	3	3080	4	10482	0	0	253	438	16	208812	21.02	0.19
Banan Branch	10	103201	Jumping Stone sub-branches	402230080344302189	Wan	23001874	357	294062	80	327320	14	100510	0	0	0	0	4	27000	10	73510	0	0	754	1205	43	721892	295.22	9.01
Banan Branch	10	100401	Wood Hole Sub-branches	402230080350688018	Tangzili	23001620	674	512739.4	295	534862	25	206080	0	0	3	18500	4	30180	18	157400	0	0	1541	2535	91	1253681	228.96	0.48

Rules, up to 3 counts per day for each transaction

Complete Monthly Statistics Tasks

A summary of the knowledge points used:

1) When the case is used;

 case  when  count  (Transqk.tran_amt) >  3  then  3  else  count  (Transqk.tran_amt)  end  count   case  when  dayqk. count  is  null  then  0  else  Dayqk.count  end  

2) The use of the left join; the Where condition can be written in the left join

--External Connection Withdrawals Left Join     (     Select         Case   when sum(Dayqkin.Count) is NULL  Then 0 Else sum(Dayqkin.Count)End Count,         Case   when sum(Dayqkin. Money) is NULL  Then 0 Else sum(Dayqkin. Money)End  Money, term_id_in from    (        Select              Case   when Count(Transqk.tran_amt)> 3  Then 3  Else Count(Transqk.tran_amt)End  Count,             Case   when sum(Transqk.tran_amt) is NULL  Then 0 Else sum(Transqk.tran_amt)End   Money, To_char (To_date (Transqk.p_req_date,'YYYYMMDD'),'DD') p_req_date, term_id_in fromBiz_oper_trans transqkwhere          --withdrawal of withdrawals by convenience(Transqk.p_trans_code='1011101' orTransqk.p_trans_code='1011231')             andTransqk.p_req_datebetweenTo_char (Add_months (Last_day (sysdate)+1,-2),'YYYYMMDD') andTo_char (Add_months (Last_day (sysdate),-1),'YYYYMMDD')        Group  byTerm_id_in,p_trans_code,to_char (To_date (Transqk.p_req_date,'YYYYMMDD'),'DD')) DayqkinGroup  bydayqkin.term_id_in) Dayqk onDayqk.term_id_in=Term. terminal_id

3) The time function indicates the first day of last month, last day of last month

between To_char (Add_months (Last_day (sysdate)+1,-2),'  YyyyMMdd' and To_char (Add_months (Last_day (sysdate),-1),' YYYYMMDD ')

4) Rounding the number of months/days

round(( Case   whenDayqk.Count  is NULL  Then 0 ElseDayqk.Count End +      Case   whenDayck.Count  is NULL  Then 0 ElseDayck.Count End +     Case   whenDayzz.Count  is NULL  Then 0 ElseDayzz.Count End +      Case   whenDaydj.Count  is NULL  Then 0 ElseDaydj.Count End +      Case   whenDaycx.Count  is NULL  Then 0 ElseDaycx.Count End )    /(1+To_char (Add_months (Last_day (sysdate),-1),'YYYYMMDD')-To_char (Add_months (Last_day (sysdate)+1,-2),'YYYYMMDD')),2)

5) The final SQL

oracle-sql-Monthly statistics on self-service terminal volume