Remaining days calculation in MySql - Multiple maturities

Question

I have the following problem when calculating the remaining days. I need the first period to expire before computing the days of the next period. In this example, even though 2 periods of 30 days were started on the same day, the duration is 60 days and therefore, as of today there are 38 days remaining, and not 16 as my formula gives. Let's imagine it as a subscription model in which a user is charged two payments for 30 days. This is my solution so far.

CREATE TEMPORARY TABLE `tmp_dates`(
  `date` datetime NOT NULL,
  `days_valid` integer NOT NULL    
);

insert into tmp_dates (date, days_valid) values ('2021-05-10', 30);
insert into tmp_dates (date, days_valid) values ('2021-05-10', 30);

SELECT sum(CASE
             WHEN Datediff(Date_add(date, INTERVAL days_valid day),
                  CURRENT_DATE) <
                  0
           THEN 0
             ELSE Datediff(Date_add(date, INTERVAL days_valid day),
                  CURRENT_DATE)
           end) AS remaining_days
FROM   tmp_dates p;

--from 2021-05-10 to 2021-06-10 (8 days remaining) + 30 (additional days remaining) = 38 days remaining

Answer 1

you need to provide more detail , but seems like you want this :

SELECT sum(days_valid) + datediff(date, CURRENT_DATE) remaining_days
FROM  tmp_dates p
group by date 

| remaining_days |
| -------------: |
|             35 |

db<>fiddle here