select mytotaltable01.lend_repay_record_id
,mytotaltable01.lend_customer_id
,mytotaltable01.core_lend_request_id
,mytotaltable01.label
,mytotaltable01.request_source
,mytotaltable01.city
,mytotaltable01.open_date
,mytotaltable01.vintage
,mytotaltable01.total_principal
,mytotaltable01.total_amount
,mytotaltable01.period
,mytotaltable01.phase
,mytotaltable01.amount
,mytotaltable01.principal
,mytotaltable01.due_date
,mytotaltable01.initial_principal
,mytotaltable01.final_principal
,mytotaltable01.repay_org
,mytotaltable01.is_outsourcing1
,mytotaltable01.repaid_time
,mytotaltable01.`status`
,mytotaltable01.repaid_type
,mytotaltable01.is_early_repayment
,mytotaltable01.repay_principal
,mytotaltable01.is_repaid_by_company
,mytotaltable01.is_early_repayment_max 
,mytotaltable01.DiffDay
,mytotaltable01.DiffDay_earliest_repaid_time
,mytotaltable01.total_repaid_phase
,mytotaltable01.total_count_repaid_times
,mytotaltable01.VARIANCE_repaid_time_day
,mytotaltable01.repaid_time_minus_due_date_0_1
,mytotaltable01.repaid_time_minus_due_date_2_7
,mytotaltable01.repaid_time_minus_due_date_8_14
,mytotaltable01.repaid_time_minus_due_date_15_30
,mytotaltable01.repaid_time_minus_due_date_30_60
,mytotaltable01.repaid_time_minus_due_date_60_90
,mytotaltable01.repaid_time_minus_due_date_90_plus
,mytotaltable01.VARIANCE_principal
,mytotaltable01.three_month_count_repaid_phase
,mytotaltable01.three_month_later_repaid_times
,mytotaltable01.three_month_sum_interest
,mytotaltable01.three_month_sum_principal
,mytotaltable01.three_month_sum_reduceOverdueFee
,mytotaltable01.three_month_sum_reducePenaltyFee
,mytotaltable01.six_month_count_repaid_phase
,mytotaltable01.six_month_later_repaid_times
,mytotaltable01.six_month_sum_interest
,mytotaltable01.six_month_sum_principal
,mytotaltable01.six_month_sum_reduceOverdueFee
,mytotaltable01.six_month_sum_reducePenaltyFee
,mytotaltable01.nine_month_count_repaid_phase
,mytotaltable01.nine_month_later_repaid_times
,mytotaltable01.nine_month_sum_interest
,mytotaltable01.nine_month_sum_principal
,mytotaltable01.nine_month_sum_reduceOverdueFee
,mytotaltable01.nine_month_sum_reducePenaltyFee
,mytotaltable01.twelve_month_count_repaid_phase
,mytotaltable01.twelve_month_later_repaid_times
,mytotaltable01.twelve_month_sum_interest
,mytotaltable01.twelve_month_sum_principal
,mytotaltable01.twelve_month_sum_reduceOverdueFee
,mytotaltable01.twelve_month_sum_reducePenaltyFee
,mytotaltable01.education
,mytotaltable01.marital_status
,mytotaltable01.gender
,mytotaltable01.income
,mytotaltable01.house_type
,mytotaltable01.job_title
,mytotaltable01.company_nature
,mytotaltable01.have_child
,mytotaltable01.car_info
,mytotaltable01.house_info
,mytotaltable01.id_no
,mytotaltable01.is_exist_qq
,mytotaltable01.is_exist_ext
,mytotaltable01.age
,date("''' + date01 + '''") as create_time
,day("''' + date01 + '''") as date
,scoreTable.yl_anti_fraud_score
,scoreTable.yl_GBDT_score
,scoreTable.yl_score
from 
((SELECT 
   lrr.id AS lend_repay_record_id,
    lrr.lend_customer_id,
    lrr.core_lend_request_id,
    clr.request_source,
  clr.city,
    lrr.pass_date AS open_date,
  year(lrr.pass_date)*100+month(lrr.pass_date) AS vintage,
    lrr.total_principal,
    lrr.total_amount,
    lrr.period,
    lrr.phase,
    lrr.amount,
    lrr.principal,
    lrr.due_date,
    lrr.initial_principal,
    lrr.final_principal,
  lrr.repay_org,
    (
        CASE WHEN mytable01.is_outsourcing = 1 THEN 1 ELSE 0 END
    ) AS 'is_outsourcing1' 
    ,
    lrr.repaid_time,
    lrr.`status`,
    lrr.repaid_type,
    lrr.is_early_repayment,
    lrr.repay_principal,
    lrr.is_repaid_by_company,
    lrr.update_time AS lend_repay_record_update_time,
    DATE_FORMAT(NOW(), '%Y-%m-%d') AS update_date,
    lrr_prev1.due_date AS 'due_date_time2',
    lrr_prev1.repaid_time AS 'repaid_past_time2',
    lrr_prev1.`status` AS 'status_past_2',
    lrr_prev2.due_date AS 'due_date_time3',
    lrr_prev2.repaid_time AS 'repaid_past_time3',
    lrr_prev2.`status` AS 'status_past_3',
    lrr_prev3.due_date AS 'due_date_time4',
    lrr_prev3.repaid_time AS 'repaid_past_time4',
    lrr_prev3.`status` AS 'status_past_4',
    lrr_future1.repaid_time AS 'repaid_futur_time1',
    lrr_future1.`status` AS 'status_futur_1',
    lrr_future2.repaid_time AS 'repaid_futur_time2',
    lrr_future2.`status` AS 'status_futur_2',
    lrr_future3.repaid_time AS 'repaid_futur_time3',
    lrr_future3.`status` AS 'status_futur_3',
     b.is_early_repayment_max AS 'is_early_repayment_max' 
    ,
    (
        CASE WHEN b.label1 = 1 THEN 1 ELSE 0 END
    ) AS label,

    (
        CASE
        WHEN ccc.max_repaid_time_staticday IS NOT NULL THEN
             datediff(date("''' + date01 + '''"),date(ccc.max_repaid_time_staticday))
        ELSE '' END
    ) AS DiffDay,
ccc.max_repaid_time_staticday as 'max_repaid_time_staticday',
    (
        CASE WHEN (   (lrr_prev1.repaid_time IS NULL
                       OR TIMESTAMPDIFF(DAY,date("''' + date01 + '''"),date(lrr_prev1.repaid_time)) >= 0
                       OR lrr_prev1.`status` = 'OVER_DUE')
                   and lrr_prev1.phase IS NOT NULL)
        THEN 1 else 0 end
    ) as 'M2',

   (
        CASE WHEN (   (   (lrr_prev2.repaid_time IS NULL
                           OR TIMESTAMPDIFF(DAY,date("''' + date01 + '''"),date(lrr_prev2.repaid_time)) >= 0
                           OR lrr_prev2.`status` = 'OVER_DUE')
                           and lrr_prev2.phase IS NOT NULL)
                   and(   (lrr_prev1.repaid_time IS NULL
                           OR TIMESTAMPDIFF(DAY,date("''' + date01 + '''"),date(lrr_prev1.repaid_time)) >= 0
                           OR lrr_prev1.`status` = 'OVER_DUE')
                           and lrr_prev1.phase IS NOT NULL)) 
                           THEN 1 else 0 end
    ) as 'M3',

   (
        CASE WHEN (   (   (lrr_prev3.repaid_time IS NULL
                           OR (TIMESTAMPDIFF(DAY,date("''' + date01 + '''"),date(lrr_prev3.repaid_time)) >= 0)
                           OR lrr_prev3.`status` = 'OVER_DUE')
                           and lrr_prev3.phase IS NOT NULL)
                   and(   (lrr_prev2.repaid_time IS NULL
                           OR TIMESTAMPDIFF(DAY,date("''' + date01 + '''"),date(lrr_prev2.repaid_time)) >= 0
                           OR lrr_prev2.`status` = 'OVER_DUE')
                           and lrr_prev2.phase IS NOT NULL)
                   and(   (lrr_prev1.repaid_time IS NULL
                           OR TIMESTAMPDIFF(DAY,date("''' + date01 + '''"),date(lrr_prev1.repaid_time)) >= 0
                           OR lrr_prev1.`status` = 'OVER_DUE')
                           and lrr_prev1.phase IS NOT NULL)) 
                           THEN 1 else 0 end
    ) as 'M4',
(CASE WHEN  (TIMESTAMPDIFF(DAY,date(lrr.due_date),date("''' + date01 + '''")) <= 15 and TIMESTAMPDIFF(DAY,date(lrr.due_date),date("''' + date01 + '''")) >0 ) then 1 else 0 end) as 'M1'
,midC.recently_repaid_time
,midC.earliest_repaid_time
,(
        CASE
        WHEN midC.recently_repaid_time IS NOT NULL THEN
             datediff(date("''' + date01 + '''"),date(midC.recently_repaid_time))
        ELSE '' END
    ) AS DiffDay_recently_repaid_time
,(
        CASE
        WHEN midC.earliest_repaid_time IS NOT NULL THEN
             datediff(date("''' + date01 + '''"),date(midC.earliest_repaid_time))
        ELSE '' END
    ) AS DiffDay_earliest_repaid_time
,midC.total_repaid_phase
,midC.total_count_repaid_times
,midC.VARIANCE_repaid_time_day
,midC.repaid_time_minus_due_date_0_1
,midC.repaid_time_minus_due_date_2_7
,midC.repaid_time_minus_due_date_8_14
,midC.repaid_time_minus_due_date_15_30
,midC.repaid_time_minus_due_date_30_60
,midC.repaid_time_minus_due_date_60_90
,midC.repaid_time_minus_due_date_90_plus
,midC.VARIANCE_principal
,infomation.education
,infomation.marital_status
,infomation.gender
,infomation.income
,infomation.house_type
,infomation.job_title
,infomation.company_nature
,infomation.have_child
,infomation.car_info
,infomation.house_info
,infomation.id_no
,(case when (infomation.qq is null or infomation.qq="")  then 0 else 1 end) as 'is_exist_qq'
,(case when (infomation.ext is null or infomation.ext="")  then 0 else 1 end) as 'is_exist_ext'
,year(curdate())-if(length(id_no)=18,substring(id_no,7,4),if(length(id_no)=15,concat('19',substring(id_no,7,2)),null)) as age
,threemonth.three_month_count_repaid_phase
,threemonth.three_month_later_repaid_times
,threemonth.three_month_sum_interest
,threemonth.three_month_sum_principal
,threemonth.three_month_sum_reduceOverdueFee
,threemonth.three_month_sum_reducePenaltyFee
,sixmonth.six_month_count_repaid_phase
,sixmonth.six_month_later_repaid_times
,sixmonth.six_month_sum_interest
,sixmonth.six_month_sum_principal
,sixmonth.six_month_sum_reduceOverdueFee
,sixmonth.six_month_sum_reducePenaltyFee
,ninemonth.nine_month_count_repaid_phase
,ninemonth.nine_month_later_repaid_times
,ninemonth.nine_month_sum_interest
,ninemonth.nine_month_sum_principal
,ninemonth.nine_month_sum_reduceOverdueFee
,ninemonth.nine_month_sum_reducePenaltyFee
,twelvemonth.twelve_month_count_repaid_phase
,twelvemonth.twelve_month_later_repaid_times
,twelvemonth.twelve_month_sum_interest
,twelvemonth.twelve_month_sum_principal
,twelvemonth.twelve_month_sum_reduceOverdueFee
,twelvemonth.twelve_month_sum_reducePenaltyFee
FROM
    finup_core.lend_repay_record AS lrr
LEFT JOIN finup_core.core_lend_request AS clr ON lrr.core_lend_request_id = clr.id
LEFT JOIN jiea.p_loan as ploan ON lrr.core_lend_request_id = ploan.core_request_id
LEFT JOIN jiea.user_info as infomation ON ploan.user_id = infomation.id
LEFT JOIN finup_core.lend_repay_record lrr_prev1 ON lrr_prev1.core_lend_request_id = lrr.core_lend_request_id AND lrr_prev1.phase = lrr.phase - 1
LEFT JOIN finup_core.lend_repay_record lrr_prev2 ON lrr_prev2.core_lend_request_id = lrr.core_lend_request_id AND lrr_prev2.phase = lrr.phase - 2
LEFT JOIN finup_core.lend_repay_record lrr_prev3 ON lrr_prev3.core_lend_request_id = lrr.core_lend_request_id AND lrr_prev3.phase = lrr.phase - 3
LEFT JOIN finup_core.lend_repay_record lrr_future1 ON lrr_future1.core_lend_request_id = lrr.core_lend_request_id AND lrr_future1.phase = lrr.phase + 1
LEFT JOIN finup_core.lend_repay_record lrr_future2 ON lrr_future2.core_lend_request_id = lrr.core_lend_request_id AND lrr_future2.phase = lrr.phase + 2
LEFT JOIN finup_core.lend_repay_record lrr_future3 ON lrr_future3.core_lend_request_id = lrr.core_lend_request_id AND lrr_future3.phase = lrr.phase + 3
LEFT JOIN (
    SELECT
        core_request_id,
        max(is_outsourcing) AS is_outsourcing
    FROM
        puhui_repay.repay_case_manage
    WHERE
        TIMESTAMPDIFF(MONTH,DATE("''' + date01 + '''"),date(create_time)) < 3
    GROUP BY
        core_request_id
) mytable01 ON lrr.core_lend_request_id = mytable01.core_request_id

LEFT JOIN (
    SELECT
        lh02.core_lend_request_id,
        max(date(lh02.repaid_time)) AS max_repaid_time,
    lh01.real_due_date AS 'real_due_date'
    FROM
        finup_core.lend_repay_record lh02
    LEFT JOIN (
        SELECT
            core_lend_request_id,
            due_date AS real_due_date
        FROM
            finup_core.lend_repay_record
        WHERE
            TIMESTAMPDIFF(DAY,date(due_date),date("''' + date01 + '''")) =1
    ) lh01 ON lh02.core_lend_request_id = lh01.core_lend_request_id
    WHERE
        TIMESTAMPDIFF(DAY,date(lh02.due_date),DATE("''' + date01 + '''")) >= 0
    AND TIMESTAMPDIFF(DAY,date(lh02.repaid_time),DATE(lh01.real_due_date)) >= 0
    AND lh02.repaid_time IS NOT NULL 
    GROUP BY
        lh02.core_lend_request_id
) a ON a.core_lend_request_id = lrr.core_lend_request_id

LEFT JOIN (
    SELECT
        hh.core_lend_request_id,
        max(date(hh.repaid_time)) AS max_repaid_time_staticday
    FROM
        finup_core.lend_repay_record hh

    WHERE
        TIMESTAMPDIFF(DAY,date(hh.repaid_time),DATE("''' + date01 + '''")) >= 1

    AND hh.repaid_time IS NOT NULL 
    GROUP BY
        hh.core_lend_request_id
) ccc ON ccc.core_lend_request_id = lrr.core_lend_request_id

LEFT JOIN (
    SELECT
        mytab003.core_lend_request_id,
        (
            CASE WHEN count((date(mytab003.repaid_time))) > 0 THEN 1 ELSE 0
            END
        ) AS label1,
        count((date(mytab003.repaid_time))) AS cnt,
        min(date(mytab003.repaid_time)) AS repaid_time_after_staticday,
        max(mytab003.is_early_repayment) AS is_early_repayment_max,
        mytab003.pass_date,
        mytab003.repaid_time,
        mytab003.real_due_date1,
        mytab003.repaid_time1,
        mytab003.`status`
    FROM
        (
            SELECT
                tab002.core_lend_request_id,
                tab002.is_early_repayment,
                tab002.pass_date,
                tab002.repaid_time,
                tab001.real_due_date1,
                tab001.repaid_time1,
                tab001.`status`
            FROM
                finup_core.lend_repay_record AS tab002
            LEFT JOIN (
                SELECT
                    core_lend_request_id,
                    due_date AS 'real_due_date1',
                    repaid_time AS 'repaid_time1',
                    `status`
                FROM
                    finup_core.lend_repay_record
                WHERE
                    TIMESTAMPDIFF(DAY,date(due_date),date("''' + date01 + '''")) =1
            ) AS tab001 ON tab002.core_lend_request_id = tab001.core_lend_request_id
        ) mytab003
    WHERE
           (
            mytab003.real_due_date1 IS NOT NULL
            AND mytab003.repaid_time IS NOT NULL
            AND (TIMESTAMPDIFF(DAY,date(mytab003.real_due_date1),date(mytab003.repaid_time)) > 0
                 AND TIMESTAMPDIFF(MONTH,DATE("''' + date01 + '''"),date(mytab003.repaid_time)) < 2 
                 AND TIMESTAMPDIFF(DAY,date('2016-01-01'),date(mytab003.pass_date)) >= 0)
            AND (mytab003.repaid_time1 IS NULL
                 OR TIMESTAMPDIFF(DAY,date(mytab003.real_due_date1),date(mytab003.repaid_time1)) > 0
                 OR mytab003.`status` = 'OVER_DUE')
            )
    GROUP BY
        mytab003.core_lend_request_id
) b ON b.core_lend_request_id = lrr.core_lend_request_id
LEFT JOIN (
select 
midtable.core_lend_request_id
,midtable.repaid_time
,midtable.due_date
,count(repaid_time) as total_repaid_phase                                                                     
,count(distinct repaid_time) as total_count_repaid_times                                                        
,max(date(midtable.repaid_time)) as recently_repaid_time
,min(date(midtable.repaid_time)) as earliest_repaid_time
,VARIANCE(midtable.repaid_time_day) as VARIANCE_repaid_time_day
,VARIANCE(midtable.principal) as VARIANCE_principal
,count(if((midtable.repaid_time_minus_due_date<=1),1,NULL)) as repaid_time_minus_due_date_0_1
,count(if(((midtable.repaid_time_minus_due_date)>1 and (midtable.repaid_time_minus_due_date)<=7),1,NULL)) as repaid_time_minus_due_date_2_7
,count(if(((midtable.repaid_time_minus_due_date)>7 and (midtable.repaid_time_minus_due_date)<=14),1,NULL)) as repaid_time_minus_due_date_8_14
,count(if(((midtable.repaid_time_minus_due_date)>14 and (midtable.repaid_time_minus_due_date)<=30),1,NULL)) as repaid_time_minus_due_date_15_30
,count(if(((midtable.repaid_time_minus_due_date)>30 and (midtable.repaid_time_minus_due_date)<=60 ),1,NULL)) as repaid_time_minus_due_date_30_60
,count(if(((midtable.repaid_time_minus_due_date)>60 and (midtable.repaid_time_minus_due_date)<=90 ),1,NULL)) as repaid_time_minus_due_date_60_90
,count(if((midtable.repaid_time_minus_due_date>90),1,NULL)) as repaid_time_minus_due_date_90_plus
from
(select core_lend_request_id
,repaid_time
,due_date
,principal
,(case when (repaid_time is not null and  TIMESTAMPDIFF(DAY,date(repaid_time),date("''' + date01 + '''")) > 0 ) then  TIMESTAMPDIFF(DAY,date(due_date),date(repaid_time)) else '' end) 
as repaid_time_minus_due_date
,day(repaid_time) as repaid_time_day
from finup_core.lend_repay_record 
where TIMESTAMPDIFF(DAY,date(repaid_time),date("''' + date01 + '''")) > 0 
) as midtable
where TIMESTAMPDIFF(DAY,date(midtable.repaid_time),date("''' + date01 + '''")) > 0 
group by midtable.core_lend_request_id) midC on midC.core_lend_request_id = lrr.core_lend_request_id
LEFT JOIN(
select
core_lend_request_id                                                                                                 
,count(repaid_time) as three_month_count_repaid_phase                                                                 
,count(distinct repaid_time) as three_month_count_repaid_times                                                        
,sum(initial_principal - final_principal) as three_month_sum_principal                                               
,sum(interest) as three_month_sum_interest                                                                           
,sum(reduceOverdueFee) as three_month_sum_reduceOverdueFee                                                           
,sum(reducePenaltyFee) as three_month_sum_reducePenaltyFee                                                           
,count(case when  (TIMESTAMPDIFF(DAY,date(due_date),date(repaid_time))>0 or repaid_time is null ) then 1 else NULL end ) as three_month_later_repaid_times
from finup_core.lend_repay_record 
where TIMESTAMPDIFF(MONTH,date(repaid_time),date("''' + date01 + '''")) > 0 and TIMESTAMPDIFF(MONTH,date(repaid_time),date("''' + date01 + '''")) <=3 and repaid_time is not null 
GROUP BY core_lend_request_id
) threemonth on threemonth.core_lend_request_id = lrr.core_lend_request_id
LEFT JOIN(
select
core_lend_request_id                                                                                               
,count(repaid_time) as six_month_count_repaid_phase                                                                
,count(distinct repaid_time) as six_month_count_repaid_times                                                       
,sum(initial_principal-final_principal) as six_month_sum_principal                                                 
,sum(interest) as six_month_sum_interest                                                                           
,sum(reduceOverdueFee) as six_month_sum_reduceOverdueFee                                                           
,sum(reducePenaltyFee) as six_month_sum_reducePenaltyFee                                                           
,count(case when  (TIMESTAMPDIFF(DAY,date(due_date),date(repaid_time))>0 or repaid_time is null ) then 1 else NULL end ) as six_month_later_repaid_times 
from finup_core.lend_repay_record 
where TIMESTAMPDIFF(MONTH,date(repaid_time),date("''' + date01 + '''")) > 0 and TIMESTAMPDIFF(MONTH,date(repaid_time),date("''' + date01 + '''")) <=6 and repaid_time is not null    
GROUP BY core_lend_request_id
) sixmonth on sixmonth.core_lend_request_id = lrr.core_lend_request_id
LEFT JOIN(
###nine month 
select
core_lend_request_id                                                                                             
,count(repaid_time) as nine_month_count_repaid_phase                                                       
,count(distinct repaid_time) as nine_month_count_repaid_times                               
,sum(initial_principal-final_principal) as nine_month_sum_principal                  
,sum(interest) as nine_month_sum_interest                                  
,sum(reduceOverdueFee) as nine_month_sum_reduceOverdueFee                 
,sum(reducePenaltyFee) as nine_month_sum_reducePenaltyFee                
,count(case when  (TIMESTAMPDIFF(DAY,date(due_date),date(repaid_time))>0 or repaid_time is null ) then 1 else NULL end ) as nine_month_later_repaid_times 
from finup_core.lend_repay_record 
where TIMESTAMPDIFF(MONTH,date(repaid_time),date("''' + date01 + '''")) > 0 and TIMESTAMPDIFF(MONTH,date(repaid_time),date("''' + date01 + '''")) <=9 and repaid_time is not null                
GROUP BY core_lend_request_id
) ninemonth on ninemonth.core_lend_request_id = lrr.core_lend_request_id
LEFT JOIN(
###twelve month 
select
core_lend_request_id                                                                                               
,count(repaid_time) as twelve_month_count_repaid_phase                                                    
,count(distinct repaid_time) as twelve_month_count_repaid_times                                          
,sum(initial_principal-final_principal) as twelve_month_sum_principal                                              
,sum(interest) as twelve_month_sum_interest                                                                        
,sum(reduceOverdueFee) as twelve_month_sum_reduceOverdueFee                                                       
,sum(reducePenaltyFee) as twelve_month_sum_reducePenaltyFee                                                         
,count(case when  (TIMESTAMPDIFF(DAY,date(due_date),date(repaid_time))>0 or repaid_time is null ) then 1 else NULL end ) as twelve_month_later_repaid_times 
from finup_core.lend_repay_record 
where TIMESTAMPDIFF(MONTH,date(repaid_time),date("''' + date01 + '''")) > 0 and TIMESTAMPDIFF(MONTH,date(repaid_time),date("''' + date01 + '''")) <=12 and repaid_time is not null                     
GROUP BY core_lend_request_id
) twelvemonth on twelvemonth.core_lend_request_id = lrr.core_lend_request_id
WHERE
    (TIMESTAMPDIFF(DAY,date(lrr.due_date),date("''' + date01 + '''")) =1
        #AND TIMESTAMPDIFF(DAY,date('2016-01-01'),date(lrr.pass_date)) >= 0
        ) 
AND(
TIMESTAMPDIFF(DAY,date(lrr_prev1.repaid_time),date("''' + date01 + '''")) >=1
    OR lrr_prev1.phase IS NULL
)
AND (lrr.repaid_time IS NULL
     OR TIMESTAMPDIFF(DAY,date(lrr.due_date),date(lrr.repaid_time)) > 0
     OR lrr.`status` = 'OVER_DUE')
AND clr.request_source IN (
    'JA_APP',
    'JA_APP_2',
    'JA_QZ_APP',
    'JA_WX',
    'JA_H5',
    'JA_JDQ',
    'JA_JLM',
    'JA_SSJ',
    'JA_WC',
    'JR_SD',
    'JA_H5_KNYSX',
    'JA_QCZJ',
    'JA_QZ',
    'JA_WCYSX',
    'JA_R360'
)
AND 
(TIMESTAMPDIFF(DAY,date("''' + date01 + '''"),date(lrr_prev3.repaid_time)) <= 0
    OR lrr_prev3.phase IS NULL
)
AND (is_early_repayment_max = 0
    OR is_early_repayment_max IS NULL
)) mytotaltable01

LEFT JOIN (
SELECT  ploan.core_request_id,
ploan.apply_no,
ploan.open_time,
ploan.source,
s.current_total as yl_score,
s.fraud_total_score as yl_anti_fraud_score,
i.`value` as yl_GBDT_score
FROM jiea.p_loan ploan
LEFT JOIN puhui_decision.decision_qian_zhan_withdraw_score s ON s.apply_no=ploan.apply_no
LEFT JOIN puhui_decision.decision_qian_zhan_withdraw_index i on i.apply_no = s.apply_no and i.`index`='GBDT00001'
WHERE ploan.source=6 
and ploan.open_time is not null
) scoreTable ON mytotaltable01.core_lend_request_id = scoreTable.core_request_id)