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

aargh just now I did read the last comment in the code...... This question needs more info!, because a column with the name `days_valid` suggest that something is valid for that number of days, and not for 30 days longer.... — Luuk, Jun 01 '21 at 16:55
I have just made some clarifications to better understand the need. — Raymont, Jun 01 '21 at 18:12
Something very similar was asked in this thread, still no answer :o https://stackoverflow.com/questions/24672511/mysql-calculating-remaining-days-of-subscriptions-based-on-payments — Raymont, Jun 01 '21 at 23:05

score 1 · Accepted Answer · answered Jun 04 '21 at 18:49

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

answered Jun 04 '21 at 18:49

eshirvana

23,227
3
22
38

1

@Raymont Can you get this Question to a status of Answer Accepted, Bounty credited to eshirvana and make it leave the Open Bounty list? – Wilson Hauck Jun 05 '21 at 01:02

Remaining days calculation in MySql - Multiple maturities

1 Answers1