Finding number of weeks between dates Oracle SQL Developer-CodePudding

If I have 2 tables:

members:

CREATE TABLE members
(member_id number,
lastname varchar2(10),
firstname varchar2(10),
license# number(9),
ST char(2),
credit_card number(12));

INSERT INTO members VALUES(10, 'Tangier', 'Tim', 111111111, 'VA', 123456789111);
INSERT INTO members VALUES(12, 'Maulder', 'Fox', 333333333, 'FL', 333333333333);
INSERT INTO members VALUES(13, 'Wild', 'Coyote', 444444444, 'VA', 444444444444);
INSERT INTO members VALUES(14, 'Joan', 'Casteel', 555555555, 'FL', 555555555555);

and rental:

CREATE TABLE rental
(rental_id number(2),
rent_date date,
return_date date,
pay_method varchar2(10),
movie_id number,
member_id number);

INSERT INTO rental VALUES(1, '18-SEP-09', '1-OCT-09', 'Credit', 11, 10);
INSERT INTO rental VALUES(2, '18-SEP-09', '3-OCT-09', 'Credit', 8, 10);
INSERT INTO rental VALUES(3, '11-OCT-09', '19-OCT-09', 'Check', 6, 12);
INSERT INTO rental VALUES(4, '28-OCT-09', '31-OCT-09', 'Debit', 3, 13);
INSERT INTO rental VALUES(5, '28-OCT-09', '5-NOV-09', 'Debit', 5, 13);
INSERT INTO rental VALUES(6, '29-OCT-09', '11-NOV-09', 'Debit', 11, 13);
INSERT INTO rental VALUES(7, '1-NOV-09', '11-NOV-09', 'Credit', 10, 14);
INSERT INTO rental VALUES(8, '2-NOV-09', '10-NOV-09', 'Credit', 12, 14);
INSERT INTO rental VALUES(9, '5-NOV-09', NULL, 'Cash', 4, 12);
INSERT INTO rental VALUES(10, '8-NOV-09', '18-NOV-09', 'Cash', 12, 12);
INSERT INTO rental VALUES(11, '14-NOV-09', NULL, 'Cash', 3, 12);
INSERT INTO rental VALUES(12, '18-NOV-09', NULL, 'Debit', 11, 13);

How can I display the number of weeks between the rent date and return date? If NULL then the return date is set to 'Dec 1, 2009' in a new column called 'delay_weeks' which should be rounded to integers. Then also a new column named 'Status' which shows if each transaction has been returned or not.

This is for a homework question but I can't seem to get it right. We are supposed to only use 1 SQL statement.

So far I have tried the following, which I think is almost correct however I get the error: "MEM"."RENTAL_ID": invalid identifier.

SELECT mem.member_id AS member_id, 
       ren.rental_id AS rental_transaction_id,
       case
       when ren.return_date IS NULL
       THEN ROUND(TO_DATE('01-dec-2009','dd-mm-yyyy')-TO_DATE(ren.rent_date, 'dd-mm-yyyy')) 
       ELSE ROUND(TO_DATE(ren.return_date, 'dd-mm-yyyy')-TO_DATE(ren.rent_date, 'dd-mm-yyyy'))
       END AS delay_weeks, 
       CASE
       WHEN ren.return_date IS NULL
       THEN 'not return'
       ELSE 'return'
       END AS status
FROM   members mem
       JOIN rental ren ON mem.member_id = ren.member_id
order BY member_id;

Any guidance on this would be appreciated. Please let me know if any clarification is needed. TIA.

CodePudding user response：

You can subtract the dates, providing a 'Dec 1, 2009' date in place of NULL return_dates, then divide by seven:

select m.member_id, r.rental_id, floor(((case when r.return_date is null then to_date('01-DEC-09') else r.return_date end) - r.rent_date)/7) weeks
from rental r join members m on r.member_id = m.member_id;

CodePudding user response：

Don't call TO_DATE on a value that is already a date; if you fix that then you get then code:

case
when ren.return_date IS NULL
THEN ROUND(TO_DATE('01-dec-09','dd-mm-yyyy')-ren.rent_date) 
ELSE ROUND(ren.return_date-ren.rent_date)
END AS delay_weeks,

If you run that then it highlights the next issue that TO_DATE('01-dec-09','dd-mm-yyyy') expects a 4-digit year but you have only provided a 2-digit year so you will get the year 9 AD rather than 2009 AD (and differences of around -730461 days). The best solution is to use a date literal (rather than trying to convert from a string):

case
when ren.return_date IS NULL
THEN ROUND(DATE '2009-12-01'-ren.rent_date) 
ELSE ROUND(ren.return_date-ren.rent_date)
END AS delay_weeks,

Then you can simplify it by using the COALESCE function rather than a CASE expression:

ROUND(COALESCE(ren.return_date, DATE '2009-12-01') - ren.rent_date)
  AS delay_weeks,

Then, when you subtract two dates you get the difference in days (rather than weeks) so you need to divide by 7:

SELECT mem.member_id AS member_id, 
       ren.rental_id AS rental_transaction_id,
       ROUND((COALESCE(ren.return_date, DATE '2009-12-01') - ren.rent_date)/7)
         AS delay_weeks, 
       CASE
       WHEN ren.return_date IS NULL
       THEN 'not return'
       ELSE 'return'
       END AS status
FROM   members mem
       JOIN rental ren ON mem.member_id = ren.member_id
order BY member_id;

If you want to count only full weeks then you want to use FLOOR. If you want to count part weeks then you want to use CEIL and if you want to count to the nearest week then use ROUND. I'm assuming that you would want either FLOOR or CEIL instead of ROUND but the question isn't clear on that requirement.

db<>fiddle here