0731 动产融资、反向&小额保理及外场客户放款额统计

--------------------------------------------------0731动产授信额度小于1000w的总放款额

SELECT substr(date_sub('{TX_DATE}', 2),1,7) AS loan_dtm, '动产融资' AS proj_type, SUM(amount)/10000 AS total_bal, COUNT(DISTINCT loan_no) AS loan_cnt FROM (SELECT t1.cust_name, t1.loan_no, t1.product_name, t1.amount, t2.* FROM (SELECT * FROM dmr_b.dmrb_rpt_risk_qj_lg_loan_s_d WHERE dt = date_sub('{TX_DATE}', 2)) t1 INNER JOIN (SELECT cust_no, product_code, project1, project2, project3, product_name FROM dmr_b.dmrb_rpt_risk_qj_lg_cust_s_d WHERE dt = date_sub('{TX_DATE}', 2) AND cancel_status = '账面核销' AND project1 IN ('动产融资') GROUP BY cust_no, product_code, project1, project2, project3, product_name) t2 ON t1.cust_no2 = t2.cust_no AND t1.product_code = t2.product_code WHERE substr(t1.start_date,1,7) = substr(date_sub('{TX_DATE}', 2),1,7)) t1 LEFT JOIN (SELECT * FROM dmr_b.dmrb_rpt_risk_dczy_lmt_s_d WHERE dt = date_sub('{TX_DATE}', 2) AND total_amount > 0) t2 ON t1.cust_name = t2.customer_name WHERE total_amount < 10000000 GROUP BY substr(date_sub('{TX_DATE}', 2),1,7), '动产融资'

-------------------------------------------------------------------------0731反向&小额保理授信额度小于1000w的总放款额

SELECT substr(date_sub('{TX_DATE}', 2),1,7) AS loan_dtm, '反向&小额保理' AS proj_type, SUM(amount)/10000 AS total_bal, COUNT(DISTINCT loan_no) AS loan_cnt FROM (SELECT t1.cust_name, t1.loan_no, t1.product_name, t1.amount, t2.* FROM (SELECT * FROM dmr_b.dmrb_rpt_risk_qj_lg_loan_s_d WHERE dt = date_sub('{TX_DATE}', 2)) t1 INNER JOIN (SELECT cust_no, product_code, project1, project2, project3, product_name FROM dmr_b.dmrb_rpt_risk_qj_lg_cust_s_d WHERE dt = date_sub('{TX_DATE}', 2) AND cancel_status = '账面核销' AND project1 IN ('保理') AND project2 = '外部保理' AND project3 IN ('小额保理', '反向保理') GROUP BY cust_no, product_code, project1, project2, project3, product_name) t2 ON t1.cust_no2 = t2.cust_no AND t1.product_code = t2.product_code WHERE substr(t1.start_date,1,7) = substr(date_sub('{TX_DATE}', 2),1,7)) t1 LEFT JOIN (SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_name ORDER BY modified_date DESC) rn FROM dmr_b.dmrb_rpt_risk_qj_acct_lmt_base_s_d WHERE dt = date_sub('{TX_DATE}', 2) AND parent_name = '保理融资' AND total_amount > 0) tmp WHERE rn = 1) t2 ON t1.cust_name = t2.customer_name WHERE total_amount < 10000000 GROUP BY substr(date_sub('{TX_DATE}', 2),1,7), '反向&小额保理'

---------------------------------------------0731外场客户放款额

SELECT loan_dtm, proj_type, SUM(loan_amt) AS total_amount, SUM(loan_cnt) AS totol_cnt FROM (SELECT CASE WHEN project1 = '保理' AND project2 IN ('内部保理', '其他内部保理') AND project3 NOT IN ('订单融资') THEN '应收池融资' WHEN project1 = '保理' AND project2 IN ('内部保理', '其他内部保理') AND project3 IN ('订单融资') THEN '订单池融资' WHEN project1 = '保理' AND project2 = '外部保理' THEN '外部保理' WHEN project1 = '采购融资' AND project2 = '金采' THEN '金采KA' WHEN project1 = '采购融资' AND project2 = '数字农贷' THEN '采购融资' WHEN project1 = '采购融资' AND project2 = '外部采购融资' AND project3 IN ('数字农贷') THEN '采购融资' WHEN project1 = '采购融资' AND project2 IN ('外部采购融资', '内部采购融资', '内部信用贷', '未来货权') AND project3 NOT IN ('数字农贷') THEN '采购融资' WHEN project1 = '动产融资' THEN '动产融资' WHEN project1 = '融资租赁' THEN '融资租赁' WHEN project1 = '信用贷款' THEN '采购融资' WHEN project1 = '农贷' AND project2 = '传统农贷' THEN '采购融资' WHEN project1 = '农贷' AND project2 = '数字农贷' THEN '采购融资' WHEN project1 = '票据' THEN '票据' WHEN project1 = '小微风险产品' AND project3 IN ('金采') THEN '小金采' WHEN project1 = '小微风险产品' AND project3 IN ('外部采购融资') THEN '采购融资' ELSE project1 END AS proj_type, * FROM dmr_b.dmrb_rpt_risk_qj_prod_loan_s_d WHERE dt = date_sub('{TX_DATE}', 2)) t1 WHERE proj_type IN ('采购融资') AND loan_dtm = substr(date_sub('{TX_DATE}', 2),1,7) GROUP BY loan_dtm, proj_type