By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table rentals as
(SELECT 1 as id, 54 as rent_id, CAST('2019-10-12' AS DATE) as start_date, CAST('2019-10-26' AS DATE) as end_date, 100 as amount
union all
SELECT 2 as id, 54 as rent_id, CAST('2019-10-13' AS DATE) as start_date, CAST('2019-10-20' AS DATE) as end_date, 150 as amount );
with recursive days (d) as
(
select CAST('2019-10-01' as date) as d
union all
select date_add(d,INTERVAL 1 DAY)
from days
where d<CAST('2019-10-31' as date)
)
select
d,
(SELECT IFNULL(SUM(amount),0) FROM rentals WHERE start_date<=d and end_date>=d) as amount
from days
Records: 2 Duplicates: 0 Warnings: 0
| d | amount |
|---|---|
| 2019-10-01 | 0 |
| 2019-10-02 | 0 |
| 2019-10-03 | 0 |
| 2019-10-04 | 0 |
| 2019-10-05 | 0 |
| 2019-10-06 | 0 |
| 2019-10-07 | 0 |
| 2019-10-08 | 0 |
| 2019-10-09 | 0 |
| 2019-10-10 | 0 |
| 2019-10-11 | 0 |
| 2019-10-12 | 100 |
| 2019-10-13 | 250 |
| 2019-10-14 | 250 |
| 2019-10-15 | 250 |
| 2019-10-16 | 250 |
| 2019-10-17 | 250 |
| 2019-10-18 | 250 |
| 2019-10-19 | 250 |
| 2019-10-20 | 250 |
| 2019-10-21 | 100 |
| 2019-10-22 | 100 |
| 2019-10-23 | 100 |
| 2019-10-24 | 100 |
| 2019-10-25 | 100 |
| 2019-10-26 | 100 |
| 2019-10-27 | 0 |
| 2019-10-28 | 0 |
| 2019-10-29 | 0 |
| 2019-10-30 | 0 |
| 2019-10-31 | 0 |