CODEWITHSUNDEEP
mysqlsqlstored-proceduresdatediff
Gary
Gary

Reputation: 39

Calculating a column value based on datediff and curdate function in SQL

Rent Table

Is it possible to calculate the below fields as follows:

if there is an integer in DateDiff then the LateFee column will be populated by (DateDiff * 3) If there is no value in DateDiff, i.e the rental is still outstanding, then the LateFee that is due currently is calculated by (Date_Due - current date) * 3

I was trying this via a stored procedure:

delimiter $$
create procedure calcLateFees()
begin 
select Rental_ID as "Rental ID", Platform_ID as "Platform ID",Title_ID as "Title ID",Date_Due as "Due Date",
Date_Returned as "Returned On", Datediff(CURDATE(), Date_Due) * 3 as "Late Fee Outstanding"
from tp_rental
where month (curdate() = month(now()) and CURDATE() > Date_Due;
end $$

call calcLateFees();

I may be going about this all wrong so any help would be greatly appreciated. Thank you in advance.

Upvotes: 0

Views: 152

Answers (1)

GMB
GMB

Reputation: 222482

if there is an integer in DayDiff then the LateFee column will be populated by (DayDiff * 3). If there is no value in DayDiff [...] then the LateFee that is due currently is calculated by (Date_Due - current date) * 3

I would translate that as the following update query:

update tp_rental
set LateFee = case
    when DayDiff is not null then DayDiff * 3
    else datediff(date_due, curdate()) * 3
end

Upvotes: 1

Related Questions