CODEWITHSUNDEEP
sqlsql-server
jreloz
jreloz

Reputation: 430

Compare two dates and compute date difference

I have to tables to compare:

Schedule table:

enter image description here

Official receipt table:

enter image description here

I just want to know if the client is paying according to his schedule by comparing the dates between Official receipt and Schedule table. if not, it will give him a penalty of $10 daily by counting the days from scheduled date.

example: the 1st schedule of payment is 2019-11-02. but the OR shows he paid on 2019-12-10. which is 38 days later than his 1st payment schedule. then penalty will be imposed. Any Idea? Thank you.

I want something like this:

Loanid     | PaymentSched | Date OR    | Past Due | Penalty
H1807.0008 | 2019-11-02   | 2019-12-10 | 38 Days  | 380

Upvotes: 0

Views: 114

Answers (2)

GMB
GMB

Reputation: 222472

Assuming that there is no missing payment and no partial payment, then one option is to enumerate the scheduled payments and receipts with row_number(), then join them together. The rest is is just filtering on late payment and computing the days late and penalty:

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

Upvotes: 1

Артемий Скриба
Артемий Скриба

Reputation: 11

datediff will help you

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]

Upvotes: 0

Related Questions