Incrementing on Date based on another field SQL-CodePudding

How is it possible to work with Dates, so I can use the number of days to add that number to another date

For example, I need to increment 1 day for each number included in Loan_period to Loan_Date and the result should be in the Return_Date

CREATE TABLE Book
  (
    Resource_ID INT PRIMARY KEY,
    Title VARCHAR2(40) NOT NULL,
    Loan_Period int (40) NOT NULL,
    noTimesLoaned int NOT NULL
  );

CREATE TABLE Loan
  (
    Loan_No   INT PRIMARY KEY,
    Loan_Date DATE NOT NULL,
    Return_Date DATE,
    Resource_ID INT NOT NULL,


  );

Thanks

CodePudding user response：

Sample data would certainly help, but - in date arithmetic, in Oracle a simple date datatype value number results in number of days added to that date. For example:

SQL> select sysdate right_now,
  2         sysdate   2 as today_plus_2_days
  3  from dual;

RIGHT_NOW           TODAY_PLUS_2_DAYS
------------------- -------------------
15.12.2021 14:02:09 17.12.2021 14:02:09

SQL>

CodePudding user response：

If you have the tables:

CREATE TABLE Book
(
  Resource_ID   INT
                GENERATED ALWAYS AS IDENTITY
                CONSTRAINT book__rid__pk PRIMARY KEY,
  Title         VARCHAR2(40) NOT NULL,
  Loan_Period   int NOT NULL,
  noTimesLoaned int NOT NULL
);

CREATE TABLE Loan (
  Loan_No     INT
              GENERATED ALWAYS AS IDENTITY
              CONSTRAINT load__ln__pk PRIMARY KEY,
  Loan_Date   DATE NOT NULL,
  Return_Date DATE,
  Resource_ID CONSTRAINT loan__rid__fk REFERENCES book
              NOT NULL
);

Then you can create a book:

INSERT INTO book(title, loan_period, notimesloaned)
VALUES ('ABC', 14, 0);

Then you can use INSERT INTO Loan (...) SELECT ... FROM book ... to transfer the correct book details into the loan table:

INSERT INTO loan (loan_date, return_date, resource_id)
SELECT TRUNC(SYSDATE),
       TRUNC(SYSDATE)   loan_period,
       resource_id
FROM   book
WHERE  resource_id = 1

Then:

SELECT * FROM loan;

Outputs:

LOAN_NO LOAN_DATE RETURN_DATE RESOURCE_ID

1 2021-12-15 00:00:00 2021-12-29 00:00:00 1

LOAN_NO	LOAN_DATE	RETURN_DATE	RESOURCE_ID
1	2021-12-15 00:00:00	2021-12-29 00:00:00	1

Note: You should not have a noTimesLoaned column in the Book table; if you want to get that information it should be generated using a COUNT(*) query on the Loan table (possibly in a view).

db<>fiddle here