How to calculate dates? 1

[mrasad]

In my database I have a table called loans, within that I have the following attributes;

create table loan
(LoanID varchar2(6) PRIMARY KEY,
Issue_Date date not null,
Due_Date date not null,
Return_Date date not null,
Fine_Total number(3,2
.....));

I want the ability to run a view (called member_fines) to find out how many days an item is late and for the number of days to be placed in a field. Is it possible to do that?
Can i do that within a view? or should that information already be placed in another table? Is it possible to increases the number of days automatically, according to the sysdate?

 

[carp]

This should get you started:

CREATE VIEW member_fines AS
SELECT loanid, sysdate - due_date days_late
FROM loan
WHERE sysdate > due_date;


Now whether or not you want to store this in a table column is another matter. As soon as you store the value, it will be out of date - unless you are truncating to the most recent whole day. But even then, storing this information is questionable.

 

[carp]

This might model your business situation more closely:

CREATE VIEW member_fines AS
SELECT loanid, ceil(sysdate - due_date) days_late
FROM loan
WHERE trunc(sysdate) > trunc(due_date);

 

[mrasad]

Thanks for that, I will build on the code that you have suggested, many many thanks.

Asad