0
table IssuedBooks
{
column RollNo,BookNo,BookName,AuthorName,IssueDate,Fine
}

I want to create a function which fills or gives the calculated Fine when called.
Suppose after 15 days of IssueDate there is a fine of 5 each day. May be I can send the IssueDate as parameter to the function and then calculate the difference between the IssueDate and Current date using DATEDIFF(DAY,IssueDate,CAST(GETDATE() as date)) and then use the number of days returned to check whether they are greater than 15 and calculate the fine accordingly and return it through function.
I am very confused about how to implement it, what will be the queries etc. Please help.
Thank you very much in advance..

animuson
  • 53,861
  • 28
  • 137
  • 147
ash
  • 156
  • 3
  • 12

1 Answers1

0
SELECT
RollNo, BookNo, BookName, AuthorName, IssueDate,
CASE
WHEN DATEDIFF(Day,IssueDate,cast(getdate() as date)) > 15
THEN (DATEDIFF(Day,IssueDate,cast(getdate() as date)) - 15) * 5
ELSE '0'
END as Fine
From IssuedBooks

Or just:

UPDATE IssuedBooks
SET Fine = (DATEDIFF(Day,IssueDate,cast(getdate() as date)) - 15) * 5
WHERE DATEDIFF(Day,IssueDate,cast(getdate() as date)) > 15
tkendrick20
  • 470
  • 4
  • 14
  • How can i achieve this through function? I want to do this through function so that I just have to call the function to update the column instead of writing the query again and again.. – ash Jul 30 '13 at 08:47
  • What DBMS are you using? – tkendrick20 Jul 30 '13 at 12:46
  • Microsoft SQL Server 2008 – ash Jul 30 '13 at 15:26
  • Easy. Look into creating a stored procedure that runs that update on your table, or whatever you want. You can run the update simply by writing `exec ProcedureName `OR better option, create a job that runs the stored procedure automatically every day, week, etc. – tkendrick20 Jul 30 '13 at 15:33
  • [This](http://stackoverflow.com/questions/287060/scheduled-run-of-stored-procedure-on-sql-server) may be of some assistance in creating the job – tkendrick20 Jul 30 '13 at 15:35