sql - 显示给定日期的天数（date_start和date_end），如果end_date结束则应用惩罚

Question

help me revise my code. this is my sql code:

    SELECT lt.loan_amount, lt.rel_date, lt.due_date,
    DATEDIFF( lt.rel_date, lt.due_date ) AS span, 
    (((DATEDIFF( lt.due_date, lt.rel_date )) + curdate()) * 0.02) AS penalty
    FROM loan_transaction lt, members m
    WHERE lt.MID = m.MID

but the output is a mess. I just wanted to mean with this that when the due_date is over, the 0.02 penalty will apply.

    (((DATEDIFF( lt.due_date, lt.rel_date )) + curdate()) * 0.02) AS penalty..

how will i show this in php mysql? I new to this. i'm actually struggling with this. Pls help. Thank you

Answer 1

I am not sure how exactly you like to put penalty, like on daily basis or one time, but you can use IF

SELECT lt.loan_amount, lt.rel_date, lt.due_date,
    DATEDIFF( lt.rel_date, lt.due_date ) AS span, 
    IF(DATEDIFF(lt.due_date, lt.rel_date) > 0, 0.02, 0) AS penalty
    FROM loan_transaction lt, members m
    WHERE lt.MID = m.MID

This is considering you are trying to compare due_date with rel_date for considering penalty. This will return penalty as 0.02 if due_date is greater than rel_date.

If you want to compare it with today's date, you can use

IF(DATEDIFF(CURDATE(), lt.due_date) > 0, 0.02, 0) AS penalty

This will return penalty as 0.02 if today's date is greater than due_date.

Edit: To apply penalty of 0.02 for each day after due_date, you should use following:

IF(DATEDIFF(CURDATE(), lt.due_date) > 0, DATEDIFF(CURDATE(), lt.due_date)*0.02, 0) AS penalty