Getting last paid month for users using mysql stored procedure

Question

I have table for Payment entry like below

id   cust_id    mnth    year    amount
--   -------    ----    ----    -----
1     250        1       2       1200

2     300        1,2     2       2400

3     450        12      3       1200

4     450        1       4       1200

5     300        3       2       1200

6     455        1       3       1200

..etc

Getting month and year from another table like below.

id     mnth

1       January

2       February

...etc

id   year 

1    2016

2    2017

3    2018

...etc

I want to get last paid month and year from first table .How to do this

First fix your design. See here: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — sticky bit, Oct 15 '19 at 10:02

Tim Biegeleisen · Answer 1 · 2019-10-15T10:11:51.713

You are storing CSV months in the mnth column, which is not optimal table design. At the very least, you should store each month in a separate row, even better would be to just store a single date for each payment. Here is one way to answer given the current design:

SELECT
    p1.id,
    p1.cust_id,
    p1.mnth,
    p1.year,
    p1.amount
FROM
(
    SELECT id, cust_id, mnth, year, amount,
        CASE WHEN CONCAT('%,', mnth, ',%') LIKE '%,12,%' THEN 12
             WHEN CONCAT('%,', mnth, ',%') LIKE '%,11,%' THEN 11
             WHEN CONCAT('%,', mnth, ',%') LIKE '%,10,%' THEN 10
             WHEN CONCAT('%,', mnth, ',%') LIKE '%,9,%' THEN 9
             WHEN CONCAT('%,', mnth, ',%') LIKE '%,8,%' THEN 8
             WHEN CONCAT('%,', mnth, ',%') LIKE '%,7,%' THEN 7
             WHEN CONCAT('%,', mnth, ',%') LIKE '%,6,%' THEN 6
             WHEN CONCAT('%,', mnth, ',%') LIKE '%,5,%' THEN 5
             WHEN CONCAT('%,', mnth, ',%') LIKE '%,4,%' THEN 4
             WHEN CONCAT('%,', mnth, ',%') LIKE '%,3,%' THEN 3
             WHEN CONCAT('%,', mnth, ',%') LIKE '%,2,%' THEN 2
             WHEN CONCAT('%,', mnth, ',%') LIKE '%,1,%' THEN 1 END AS mnth_num
    FROM Payment
) p1
INNER JOIN
(
    SELECT
        MAX(CASE WHEN CONCAT('%,', mnth, ',%') LIKE '%,12,%' THEN 12
                 WHEN CONCAT('%,', mnth, ',%') LIKE '%,11,%' THEN 11
                 WHEN CONCAT('%,', mnth, ',%') LIKE '%,10,%' THEN 10
                 WHEN CONCAT('%,', mnth, ',%') LIKE '%,9,%' THEN 9
                 WHEN CONCAT('%,', mnth, ',%') LIKE '%,8,%' THEN 8
                 WHEN CONCAT('%,', mnth, ',%') LIKE '%,7,%' THEN 7
                 WHEN CONCAT('%,', mnth, ',%') LIKE '%,6,%' THEN 6
                 WHEN CONCAT('%,', mnth, ',%') LIKE '%,5,%' THEN 5
                 WHEN CONCAT('%,', mnth, ',%') LIKE '%,4,%' THEN 4
                 WHEN CONCAT('%,', mnth, ',%') LIKE '%,3,%' THEN 3
                 WHEN CONCAT('%,', mnth, ',%') LIKE '%,2,%' THEN 2
                 WHEN CONCAT('%,', mnth, ',%') LIKE '%,1,%' THEN 1 END) AS max_mnth,
        MAX(year) AS max_year
    FROM Payment
    WHERE year = (SELECT MAX(year) FROM Payment)
) p2
    ON p1.year = p2.max_year AND
       p1.mnth_num = p2.max_mnth;

Getting last paid month for users using mysql stored procedure

1 Answers1

Demo