Distinct days with a certain status - SQL Oracle

Question

I have a table (rent) like this:

user_rent| book_rent | rent_from  | rent_to
-------------------------------------------
John Doe | Hobbit    | 01.04.2018 | 18.05.2018
Jane Doe | Inferno   | 12.07.2018 | 25.07.2018
Jane Doe | Hamlet    | 12.07.2018 | 05.08.2018
Sara Doe | Macbeth   | 01.06.2018 | 08.06.2018
Sara Doe | Othello   | 08.06.2018 | 15.06.2018

What I need is sum of days each user had a book rent under his name within a certain period. Period in question is 01.05.2018 - 31.07.2018

There are three distinct problems here.

1. Book can be rented before, or returned later, but I need to count days only in this time frame (ie. John Doe has only 18 days within that time frame - 01.05.2018 - 18.05.2018)
2. Both the starting and ending date needs to enter the sum (ie. 01.06.2018 - 03.06.2018 are three days, not two)
   That becomes a problem if one rents new book immediately (ie. Sara Doe should have 15 days 1-8 and 8-15)
3. Some users can have two books with overlapping days, but I need only distinct days (ie. Jane Doe should have 20 days - 12.07.2018 - 31.07.2018

My result table should look like this:

user_rent| days
-----------------
John Doe | 18
Jane Doe | 20
Sara Doe | 15

But I can't get it :(
I'd appreciate any help.

Edit: This was my attempt which solved only first problem...

SELECT USER_RENT, SUM(DIFF) DAYS
FROM
(
  SELECT DISTINCT(USER_RENT), REAL_START, REAL_END, REAL_END-REAL_START+1 DIFF
  FROM
  (
    SELECT DISTINCT(USER_RENT), 
           CASE WHEN RENT_FROM < '01.05.2018' 
                THEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE, -4))+1) 
                ELSE RENT_FROM 
                END REAL_START,
           CASE WHEN RENT_TO > '31.07.2018' 
                THEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE, -1))) 
                ELSE RENT_TO 
                END REAL_END
    FROM RENT
    WHERE RENT_TO > '30.04.2018'
    AND RENT_FROM < '01.08.2018'
  )
)
GROUP BY USER_RENT
;

Answer 1

You can use a hierarchical query to expand your date range to all its individual days:

select date '2018-05-01' + level - 1
from dual
connect by level <= date '2018-07-31' - date '2018-05-01' + 1

You can then use that as an inline view or CTE, and join to the rent table based on those dates:

with days (day) as (
  select date '2018-05-01' + level - 1
  from dual
  connect by level <= date '2018-07-31' - date '2018-05-01' + 1
)
select r.user_name, count(distinct d.day)
from days d
join rent r
on r.rent_from <= d.day
and r.rent_to >= d.day
group by r.user_name;

or from 12c you could do the same thing with cross apply:

select r.user_name, count(distinct d.day) as days
from rent r
cross apply (
  select date '2018-05-01' + level - 1 as day
  from dual
  connect by level <= date '2018-07-31' - date '2018-05-01' + 1
) d
where r.rent_from <= d.day
and r.rent_to >= d.day
group by r.user_name
order by r.user_name;

Demo with your sample data in another CTE:

with rent (user_name, book, rent_from, rent_to) as (
            select 'John Doe', 'Hobbit',  date '2018-04-01', date '2018-05-18' from dual
  union all select 'Jane Doe', 'Inferno', date '2018-07-12', date '2018-07-25' from dual
  union all select 'Jane Doe', 'Hamlet',  date '2018-07-12', date '2018-08-05' from dual
  union all select 'Sara Doe', 'Macbeth', date '2018-06-01', date '2018-06-08' from dual
  union all select 'Sara Doe', 'Othello', date '2018-06-08', date '2018-06-15' from dual
),
days (day) as (
  select date '2018-05-01' + level - 1
  from dual
  connect by level <= date '2018-07-31' - date '2018-05-01' + 1
)
select r.user_name, count(distinct d.day) as days
from days d
join rent r
on r.rent_from <= d.day
and r.rent_to >= d.day
group by r.user_name
order by r.user_name;

USER_NAME       DAYS
--------- ----------
Jane Doe          20
John Doe          18
Sara Doe          15

I've changed the column names in your sample to legal values.