how to show the next month using concat, month 12 (dec) following month became 13

Question

I have a table like this.

employee_id	join_year	join_month	joindatetime	first_name	last_name
1	2011	03	2011-03-06 06:00:00	Mike	Jordan
2	2012	11	2012-11-19 07:00:00	John	Simpson
31	2019	12	2019-12-05 11:30:00	Jane	Athur

and I need to add a column using concat a couple of columns and one special requirement is that join_month is to show the following month.

For example, join_month is 03 (for Mike) + 1 (adding 1 to get the following month) so outcome should be 04. and if join_month is 11 + 1 (following month) so outcome should be 12

so the expected outcome should be

1-20110401-Mike-Jordan
2-20121201-John-Simpson
31-20200101-Jane-Athur

so with my limited knowledge, I used the following method

concat(employee_id,'-',join_year,join_month+1,'01','-',firstname,'-',lastname) as 'WWID' 

however, I am getting the following result.

1-2011401-Mike-Jordan
2-20121201-John-Simpson
31-20191301-Jane-Athur

2 issues here.

1st issue : 2 digits of month is becoming 1 digit for the first line (04 > 4)

2nd issue : following month of 201912 (dec) should be following year of 202001 (jan) but it comes out as 201913 instead..

is there any way to achieve above expeced outcome? Pleae help.

Thank you

Answer 1

You need to handle the special case when the month is 12. The month number wraps around to 1, and the year has to increment. You can use IF or CASE for this.

concat(employee_id,'-',
    IF(join_month = 12, join_year+1, join_year),
    IF(join_month = 12, 1, join_month+1),
    '01','-',firstname,'-',lastname) as 'WWID' 

Answer 2

mysql> select extract(year_month from date_add('2019-12-05', interval 1 month)) as ym;
+--------+
| ym     |
+--------+
| 202001 |
+--------+

Answer 3

WITH tbl (employee_id, join_year, join_month, joindatetime, first_name, last_name) AS (
    VALUES
        ROW( 1, 2011, 03, '2011-03-06 06:00:00', 'Mike', 'Jordan'),
        ROW( 2, 2012, 11, '2012-11-19 07:00:00', 'John', 'Simpson'),
        ROW(31, 2019, 12, '2019-12-05 11:30:00', 'Jane', 'Athur')
)
SELECT CONCAT_WS('-', employee_id, DATE_FORMAT(joindatetime + INTERVAL 1 MONTH, '%Y%m01'), first_name, last_name) AS WWID
FROM tbl;

Outputs:

WWID
1-20110401-Mike-Jordan
2-20121201-John-Simpson
31-20200101-Jane-Athur