历史数据回溯

BEGIN
-- 按日回退还款明细数据,start_date格式(2019-05-01)
declare start_date date;
set @start_date = daydate;
  -- drop table if EXISTS repayment_detail_day;
  -- create table repayment_detail_day as
insert into repayment_detail_day
select a.*,
       case when  date(当前计划还款日) >= @start_date then 0
            when  date(还款日期) is null then 1
            when  date(还款日期) is not null and  date(还款日期)<= date(当前计划还款日) then 0
            when  date(还款日期) is not null and  date(还款日期)> date(当前计划还款日) and @start_date> date(还款日期) then 0
            when  date(还款日期) is not null and  date(还款日期)> date(当前计划还款日) and @start_date<= date(还款日期) then  1
        end as 当前是否逾期,
       case when  date(当前计划还款日) >= @start_date then 0
            when  date(还款日期) is null then to_days(@start_date)-to_days( date(当前计划还款日))
            when  date(还款日期) is not null and  date(还款日期)<= date(当前计划还款日) then 0
            when  date(还款日期) is not null and  date(还款日期)> date(当前计划还款日) and @start_date> date(还款日期) then to_days( date(还款日期))-to_days( date(当前计划还款日))
            when  date(还款日期) is not null and  date(还款日期)> date(当前计划还款日) and @start_date<= date(还款日期) then to_days(@start_date)-to_days( date(当前计划还款日))
       end as 付清时或当前逾期天数,
       @start_date 当前日期
from (
      select c.contract_id 合同ID,
             c.customer_num 客户编号,
             d.repaying_plan_detail_id 还款计划明细ID,
             d.current_period 当前期数,
             d.current_end_date 当前计划还款日,
             d.current_principal 当前应还本金,
             d.pay_loan_date 还款日期,
             d.repayed_principal 已还本金
       from repaying_plan c
       join repaying_plan_detail d 
	     on c.repaying_plan_id=d.repaying_plan_id
	   where pay_loan_date < @start_date
      order by contract_id,repaying_plan_id,current_period ) a
order by 合同ID,当前期数;
END