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 |