# Calculating membership expiry for overlapping subscriptions

## All we need is an easy explanation of the problem, so here it is.

The setup: people pay for membership subscriptions, they can pay before or after their expiry date, so there are gaps and overlaps. In case of a gap, subscription starts anew, in case of overlapping it adds to the current one. Here’s how the table looks like:

So for each new renewal we save a datestamp and its term in months then calculate current expiry on the fly, here’s a snippet of code that produces an actual expiry for every account.

``````SELECT max(z.expiry) as expiry, z.user_id FROM (
SELECT @exp := IF(user_id = @usr, GREATEST(@exp, created), created) + INTERVAL term MONTH AS expiry,
@usr := user_id AS user_id
FROM memrecords
JOIN (SELECT @exp := "1975-01-01 00:00:00", @usr := 0) dummy
ORDER BY user_id ASC, created ASC) AS z
GROUP BY z.user_id;
``````

So far it works kinda all right but I wanted to optimise things a bit by storing expiry dates in a separate table updated with triggers.

The problem is that a) MySQL 8 fires warnings about setting variables in expressions being deprecated, b) I feel that it’s a job for a recursive cte and/or window functions but can’t figure it out myself.

Any ideas are welcome!

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

``````WITH RECURSIVE
cte1 AS (  -- enumerate rows
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created) rn
FROM memrecords
),
cte2 AS (  -- calculate expire date recursively
SELECT *,
created + INTERVAL term MONTH AS expired
FROM cte1
WHERE rn = 1
UNION ALL
SELECT cte1.*,
GREATEST(cte2.expired, cte1.created) + INTERVAL cte1.term MONTH
FROM cte1
JOIN cte2 USING (user_id)
WHERE cte1.rn = cte2.rn + 1
),
cte3 AS (  -- get last row number
SELECT user_id,
MAX(rn) rn
FROM cte1
GROUP BY 1
)
-- obtain needed data
SELECT user_id, cte2.expired
FROM cte3
NATURAL JOIN cte2
``````

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ca1f1e0b931b1a500fd6bc5604b41f07

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂