MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- source data

CREATE TABLE `item` (
  `id` int NOT NULL AUTO_INCREMENT,
  `brand` varchar(45) DEFAULT NULL,
  `count` int DEFAULT NULL,
  `specialId` varchar(45) DEFAULT NULL,
  `other_data` varchar(45) DEFAULT NULL,
  `activity_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`));

INSERT INTO `item` VALUES 
(1,'happyInc',2,'1234567890','something','2020-04-30 00:00:00'),
(2,'happyInc',10,'1234567890','something','2020-05-31 00:00:00'),
(3,'happyInc',30,'1234567890','something','2020-06-30 00:00:00'),
(4,'happyInc',23,'1234567890','something','2020-07-31 00:00:00'),
(5,'happyInc',4,'5555555555','something','2020-05-31 00:00:00'),
(6,'happyInc',7,'5555555555','something','2020-07-31 00:00:00'),
(7,'happyInc',1,'5555555555','something','2020-10-31 00:00:00'),
(8,'happyInc',4,'5555555555','something','2020-11-30 00:00:00'),
(9,'happyInc',3,'9999999999','something','2019-12-31 00:00:00'),
(10,'happyInc',19,'9999999999','something','2021-03-31 00:00:00');

SELECT * FROM item;


id
brand
count
specialId
other_data
activity_date


1
happyInc
2
1234567890
something
2020-04-30 00:00:00

2
happyInc
10
1234567890
something
2020-05-31 00:00:00

3
happyInc
30
1234567890
something
2020-06-30 00:00:00

4
happyInc
23
1234567890
something
2020-07-31 00:00:00

5
happyInc
4
5555555555
something
2020-05-31 00:00:00

6
happyInc
7
5555555555
something
2020-07-31 00:00:00

7
happyInc
1
5555555555
something
2020-10-31 00:00:00

8
happyInc
4
5555555555
something
2020-11-30 00:00:00

9
happyInc
3
9999999999
something
2019-12-31 00:00:00

10
happyInc
19
9999999999
something
2021-03-31 00:00:00


-- components which will be used

-- the query which generates numbers. Will be used triple 
-- which gives values 0..124, i.e. ~10 years.

SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4


num


0

1

2

3

4


-- generate last day of month in all dates range

SELECT LAST_DAY(date1.`date` + INTERVAL num1.num + 5*num2.num + 25*num3.num MONTH) activity_date,
       date1.last_date
FROM (SELECT MIN(activity_date) `date`, LAST_DAY(MAX(activity_date)) last_date
      FROM item) date1
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) num1
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) num2
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) num3
HAVING activity_date <= last_date
ORDER BY 1

-- ORDER BY is added for output data clarity and is not used later

-- If needed more then 10-year range then expand up to 0..5
-- and adjust multipliers, this will give 18-year range


activity_date
last_date


2019-12-31
2021-03-31

2020-01-31
2021-03-31

2020-02-29
2021-03-31

2020-03-31
2021-03-31

2020-04-30
2021-03-31

2020-05-31
2021-03-31

2020-06-30
2021-03-31

2020-07-31
2021-03-31

2020-08-31
2021-03-31

2020-09-30
2021-03-31

2020-10-31
2021-03-31

2020-11-30
2021-03-31

2020-12-31
2021-03-31

2021-01-31
2021-03-31

2021-02-28
2021-03-31

2021-03-31
2021-03-31


-- select values pairs present in the data

SELECT DISTINCT brand, specialId
FROM item


brand
specialId


happyInc
1234567890

happyInc
5555555555

happyInc
9999999999


-- final query

SELECT item.id,
       brand,
       item.`count`,
       specialId,
       item.other_data,
       activity_date
FROM ( SELECT LAST_DAY(date1.`date` + INTERVAL num1.num + 5*num2.num + 25*num3.num MONTH) activity_date,
              date1.last_date
       FROM (SELECT MIN(activity_date) `date`, LAST_DAY(MAX(activity_date)) last_date
             FROM item) date1
       CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) num1
       CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) num2
       CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) num3
       HAVING activity_date <= last_date
     ) date2
CROSS JOIN ( SELECT DISTINCT brand, specialId
             FROM item
            ) spId
LEFT JOIN item USING (brand, specialId, activity_date)
ORDER BY 4,6


id
brand
count
specialId
other_data
activity_date


null
happyInc
null
1234567890
null
2019-12-31

null
happyInc
null
1234567890
null
2020-01-31

null
happyInc
null
1234567890
null
2020-02-29

null
happyInc
null
1234567890
null
2020-03-31

1
happyInc
2
1234567890
something
2020-04-30

2
happyInc
10
1234567890
something
2020-05-31

3
happyInc
30
1234567890
something
2020-06-30

4
happyInc
23
1234567890
something
2020-07-31

null
happyInc
null
1234567890
null
2020-08-31

null
happyInc
null
1234567890
null
2020-09-30

null
happyInc
null
1234567890
null
2020-10-31

null
happyInc
null
1234567890
null
2020-11-30

null
happyInc
null
1234567890
null
2020-12-31

null
happyInc
null
1234567890
null
2021-01-31

null
happyInc
null
1234567890
null
2021-02-28

null
happyInc
null
1234567890
null
2021-03-31

null
happyInc
null
5555555555
null
2019-12-31

null
happyInc
null
5555555555
null
2020-01-31

null
happyInc
null
5555555555
null
2020-02-29

null
happyInc
null
5555555555
null
2020-03-31

null
happyInc
null
5555555555
null
2020-04-30

5
happyInc
4
5555555555
something
2020-05-31

null
happyInc
null
5555555555
null
2020-06-30

6
happyInc
7
5555555555
something
2020-07-31

null
happyInc
null
5555555555
null
2020-08-31

null
happyInc
null
5555555555
null
2020-09-30

7
happyInc
1
5555555555
something
2020-10-31

8
happyInc
4
5555555555
something
2020-11-30

null
happyInc
null
5555555555
null
2020-12-31

null
happyInc
null
5555555555
null
2021-01-31

null
happyInc
null
5555555555
null
2021-02-28

null
happyInc
null
5555555555
null
2021-03-31

9
happyInc
3
9999999999
something
2019-12-31

null
happyInc
null
9999999999
null
2020-01-31

null
happyInc
null
9999999999
null
2020-02-29

null
happyInc
null
9999999999
null
2020-03-31

null
happyInc
null
9999999999
null
2020-04-30

null
happyInc
null
9999999999
null
2020-05-31

null
happyInc
null
9999999999
null
2020-06-30

null
happyInc
null
9999999999
null
2020-07-31

null
happyInc
null
9999999999
null
2020-08-31

null
happyInc
null
9999999999
null
2020-09-30

null
happyInc
null
9999999999
null
2020-10-31

null
happyInc
null
9999999999
null
2020-11-30

null
happyInc
null
9999999999
null
2020-12-31

null
happyInc
null
9999999999
null
2021-01-31

null
happyInc
null
9999999999
null
2021-02-28

10
happyInc
19
9999999999
something
2021-03-31

id	brand	count	specialId	other_data	activity_date
1	happyInc	2	1234567890	something	2020-04-30 00:00:00
2	happyInc	10	1234567890	something	2020-05-31 00:00:00
3	happyInc	30	1234567890	something	2020-06-30 00:00:00
4	happyInc	23	1234567890	something	2020-07-31 00:00:00
5	happyInc	4	5555555555	something	2020-05-31 00:00:00
6	happyInc	7	5555555555	something	2020-07-31 00:00:00
7	happyInc	1	5555555555	something	2020-10-31 00:00:00
8	happyInc	4	5555555555	something	2020-11-30 00:00:00
9	happyInc	3	9999999999	something	2019-12-31 00:00:00
10	happyInc	19	9999999999	something	2021-03-31 00:00:00

activity_date	last_date
2019-12-31	2021-03-31
2020-01-31	2021-03-31
2020-02-29	2021-03-31
2020-03-31	2021-03-31
2020-04-30	2021-03-31
2020-05-31	2021-03-31
2020-06-30	2021-03-31
2020-07-31	2021-03-31
2020-08-31	2021-03-31
2020-09-30	2021-03-31
2020-10-31	2021-03-31
2020-11-30	2021-03-31
2020-12-31	2021-03-31
2021-01-31	2021-03-31
2021-02-28	2021-03-31
2021-03-31	2021-03-31

id	brand	count	specialId	other_data	activity_date
null	happyInc	null	1234567890	null	2019-12-31
null	happyInc	null	1234567890	null	2020-01-31
null	happyInc	null	1234567890	null	2020-02-29
null	happyInc	null	1234567890	null	2020-03-31
1	happyInc	2	1234567890	something	2020-04-30
2	happyInc	10	1234567890	something	2020-05-31
3	happyInc	30	1234567890	something	2020-06-30
4	happyInc	23	1234567890	something	2020-07-31
null	happyInc	null	1234567890	null	2020-08-31
null	happyInc	null	1234567890	null	2020-09-30
null	happyInc	null	1234567890	null	2020-10-31
null	happyInc	null	1234567890	null	2020-11-30
null	happyInc	null	1234567890	null	2020-12-31
null	happyInc	null	1234567890	null	2021-01-31
null	happyInc	null	1234567890	null	2021-02-28
null	happyInc	null	1234567890	null	2021-03-31
null	happyInc	null	5555555555	null	2019-12-31
null	happyInc	null	5555555555	null	2020-01-31
null	happyInc	null	5555555555	null	2020-02-29
null	happyInc	null	5555555555	null	2020-03-31
null	happyInc	null	5555555555	null	2020-04-30
5	happyInc	4	5555555555	something	2020-05-31
null	happyInc	null	5555555555	null	2020-06-30
6	happyInc	7	5555555555	something	2020-07-31
null	happyInc	null	5555555555	null	2020-08-31
null	happyInc	null	5555555555	null	2020-09-30
7	happyInc	1	5555555555	something	2020-10-31
8	happyInc	4	5555555555	something	2020-11-30
null	happyInc	null	5555555555	null	2020-12-31
null	happyInc	null	5555555555	null	2021-01-31
null	happyInc	null	5555555555	null	2021-02-28
null	happyInc	null	5555555555	null	2021-03-31
9	happyInc	3	9999999999	something	2019-12-31
null	happyInc	null	9999999999	null	2020-01-31
null	happyInc	null	9999999999	null	2020-02-29
null	happyInc	null	9999999999	null	2020-03-31
null	happyInc	null	9999999999	null	2020-04-30
null	happyInc	null	9999999999	null	2020-05-31
null	happyInc	null	9999999999	null	2020-06-30
null	happyInc	null	9999999999	null	2020-07-31
null	happyInc	null	9999999999	null	2020-08-31
null	happyInc	null	9999999999	null	2020-09-30
null	happyInc	null	9999999999	null	2020-10-31
null	happyInc	null	9999999999	null	2020-11-30
null	happyInc	null	9999999999	null	2020-12-31
null	happyInc	null	9999999999	null	2021-01-31
null	happyInc	null	9999999999	null	2021-02-28
10	happyInc	19	9999999999	something	2021-03-31