小弟在统计临柜汇出汇款笔数、金额时卡住了.....
想询问版上的大大,下图为最终显示结果
小弟该如何下指令会比较好....
下方是小弟目前算出汇出的币别并折成美金
SELECT A.REMIT_CCY,COUNT(0) ,
CASE WHEN REMIT_CCY ='AUD' THEN SUM(ROUND(REMIT_AMT * B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='CAD' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='CHF' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='CNY' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='EUR' THEN SUM(ROUND(REMIT_AMT * B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='GBP' THEN SUM(ROUND(REMIT_AMT * B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='HKD' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='JPY' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='NZD' THEN SUM(ROUND(REMIT_AMT * B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='SGD' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='ZAR' THEN SUM(ROUND(REMIT_AMT / B.EXRATE_EVALUATE,2))
WHEN REMIT_CCY ='USD' THEN SUM(ROUND(REMIT_AMT * B.EXRATE_EVALUATE,2)) END AS 折美金额
FROM FRORTM A
LEFT JOIN CMXRVM_CBUS B ON A.REMIT_CCY=B.CCY AND B.VALID_DATE ='20240200'
WHERE TXNT_DATE>='20240201' and TXNT_DATE<='20240231' GROUP BY REMIT_CCY ORDER BY REMIT_CCY
下图是区分法金 / 个金 判别方式
select CLIENT_CODE,sum(CNT) from ( select case when SUBSTRING(REMITTER_ID,9,1)=' ' then '法金' else '个金' end as CLIENT_CODE, count(*) as CNT from FRORTM where TXNT_DATE>='20240201' and TXNT_DATE<='20240231' group by SUBSTRING(REMITTER_ID,9,1) ) as d group by CLIENT_CODE ORDER BY 1