--------------
|
SET @asOfDate='2020-05-21',@asOfDateOffsetMeta='2020-05-21'
|
--------------
|
Query OK, 0 rows affected (0.000 sec)
|
--------------
|
EXPLAIN SELECT
|
ti.type_id AS ti_type_id, ti.sub_type_id AS ti_sub_type_id,
|
th.id AS hdr_id,
|
ti.id AS ti_id,
|
IF(IFNULL(lf.id,0) >0,CONCAT(lf.id,'-',tip.paid_type_id,'-',paid_sub_type.id),CONCAT(th.id,'-',paid_type.id,'-',IFNULL(paid_sub_type.id,0))) AS lf_id,
|
sl.local_name AS lm_name,
|
IF(tip.paid_type_id IN (29,51),'Prin',
|
IF(paid_sub_type.id = 126,'Lien Fee',IF(lf.id IS NULL,paid_type.sub_type,CASE WHEN paid_sub_type.id = 271 AND tip.paid_type_id IN (31,32) THEN 'Pawn Fee' WHEN paid_sub_type.id = 282 AND tip.paid_type_id = 32 THEN 'Expired Fee' END))) AS fee_name,
|
(CASE WHEN paid_sub_type.id = 282 AND tip.paid_type_id = 32 THEN 'Expired Fee' ELSE paid_sub_type.type END) AS sub_type,
|
lf.last_earn_date,
|
lf.fee_apr,
|
IF( tip.paid_sub_type_id IN (214,215,230),0.0000, tip.paid_amt) AS paid_amt,
|
0 AS accrued_amt,
|
(
|
IF(ti.bus_date=@asOfDate AND ti_type.ttl_oper>0,tip.paid_amt,0)
|
*
|
IF( tip.paid_sub_type_id IN (214,215,230,213,282,145,144,143),0.0000, 1)
|
) AS applied_amt,
|
IF(ti.bus_date=@asOfDateOffsetMeta AND ti_type.ttl_oper<0,tip.paid_amt,0) AS pmts_applied,
|
th.effective_due_date AS due_date,
|
DATEDIFF(@asOfDate,th.effective_due_date) AS days_late,
|
IF(ti.bus_date=@asOfDateOffsetMeta AND ti_type.ttl_oper<0,ti.over_pmt_amt,0) AS over_pmt_amt,
|
0 AS billed_amt,
|
0 AS unbilled_amt,
|
0 AS credit_limit_amt,
|
0 AS available_bal,
|
0 AS average_bal
|
FROM
|
cs_trans_item ti
|
INNER JOIN tmp_tb_ldh_GPawnv3 th ON ti.hdr_id=th.id
|
INNER JOIN cs_trans_item_type ti_type ON ti.type_id=ti_type.id
|
INNER JOIN cs_store_loan sl ON th.loan_model_id=sl.id
|
INNER JOIN cs_trans_status ts ON th.status_id=ts.id
|
INNER JOIN cs_trans_item_paid tip ON ti.id=tip.trans_id
|
INNER JOIN cs_trans_item_type paid_type ON tip.paid_type_id=paid_type.id
|
LEFT JOIN cs_loan_fee lf ON tip.paid_lf_id=lf.id
|
LEFT JOIN cs_store_loan_fee slf ON lf.store_fee_id=slf.id
|
LEFT JOIN cs_trans_item_sub_type AS paid_sub_type ON tip.paid_sub_type_id=paid_sub_type.id
|
WHERE ti.is_void=0
|
AND ti.bus_date BETWEEN '2020-08-01' AND '2020-09-21'
|
AND ti_type.ttl_oper<>0
|
--------------
|
+------+-------------+---------------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------+
|
| 1 | SIMPLE | ti | ALL | NULL | NULL | NULL | NULL | 2000 | Using where with pushed condition |
|
| 1 | SIMPLE | th | ALL | NULL | NULL | NULL | NULL | 2000 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | ti_type | ALL | NULL | NULL | NULL | NULL | 2000 | Using where with pushed condition; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | sl | ALL | NULL | NULL | NULL | NULL | 2000 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | ts | ALL | NULL | NULL | NULL | NULL | 2000 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | tip | ALL | NULL | NULL | NULL | NULL | 2000 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | paid_type | ALL | NULL | NULL | NULL | NULL | 2000 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | lf | ALL | NULL | NULL | NULL | NULL | 2000 | Using where |
|
| 1 | SIMPLE | slf | ALL | NULL | NULL | NULL | NULL | 2000 | Using where |
|
| 1 | SIMPLE | paid_sub_type | ALL | NULL | NULL | NULL | NULL | 2000 | Using where |
|
+------+-------------+---------------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------+
|
10 rows in set (0.060 sec)
|
Bug
Blocker