假设没有遗漏付款和部分付款，那么一个选项是用 row_number() ，然后将它们连接在一起。剩下的只是过滤逾期付款，计算逾期天数和罚款：

select 
    s.loan_id,
    s.date_payment,
    r.date_or,
    datediff(day, s.date_payment, r.date_or) as past_due_days,
    10 * datediff(day, s.date_payment, r.date_or) as penalty
from (
    select s.*, row_number() over(partition by loan_id order by date_payment) rn
    from schedule s
    where total_payment > 0
) s
inner join (
    select r.*, row_number() over(partition by loan_id order by date_or) rn
    from official_receipt r
) r on s.loan_id = r.loan_id and s.rn = r.rn and s.total_payment = r.amount
where r.date_or > s.date_payment

datediff会帮你的

select datediff(day,'2019-10-02','2019-12-10')

select [Loanid],[PaymentSched],[Date OR],datediff(day,[PaymentSched],[Date OR]) as [Past Due],datediff(day,[PaymentSched],[Date OR])*10 as Penalty
from Schedule s
join [Official receipt] o on o.[Loanid]=s.[Loanid]