--------------

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)