0

I want to create a stored procedure to update a column based on an amount of time. For example, to update the interest generated column every 15 days.

Here is my code. Please help.

create table Loan(
Loan_ID int not null primary key,
Loan_custID int not null foreign key references Customers(Cust_ID),
Loan_Amount int not null,
Loan_Interest int not null,
Loan_Date date not null unique,
)

Create table Interestgenerated(
IG_ID int not null primary key,
Loan_ID int not null foreign key references Loan1(Loan_ID),
Loan_Date date null foreign key references Loan1(Loan_Date),
IG_Amount int not null,
IG_Date datetime not null
)
create procedure InsertINtoInterestgenerated1
@PresentDate  Datetime

as
set @PresentDate=getdate()


select Loan_ID from Loan
set IG_Date=Loan_Date
IG_Date=dateadd(day,15, IG_Date)
if @PresentDate=IG_Date
begin
update Interestgenerated1 set IG_Date = @PresentDate, IG_Amount=IG_Amount*0.15
end
Pang
  • 9,564
  • 146
  • 81
  • 122
Likilik Zipzip
  • 15
  • 6
  • 1
    What is your question? – Xedni Oct 12 '18 at 03:14
  • `update the interest generated colum every 15 days.`. What will happen if somebody execute the query that updates that more than once in 15 days ? I would make a function to calculate the interest based on the base line date and current date – Squirrel Oct 12 '18 at 03:44
  • I want it to do that automatically so it won't depend on nobody, It will only depend on the Loan_date. I mean every 15 days after the Loan date it automatically generate the interest. – Likilik Zipzip Oct 12 '18 at 12:13

1 Answers1

0

Considering you want to automate the update of the value in column IG_Amount every 15 days, you can schedule a job to run every 15 days at midnight like on the 1st and 16th of every month.

the below link might help you:

how to schedule a job for sql query to run daily?

AmeyaN99
  • 172
  • 4