By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE salesdata (
id VARCHAR(2) NOT NULL,
date DATE NOT NULL,
stock SMALLINT UNSIGNED,
sold SMALLINT UNSIGNED
);
INSERT INTO salesdata VALUES
('a1', '2024-01-01', 2, 28),
('a1', '2024-08-01', 12, 100),
('a1', '2024-04-01', 12, 28),
('a3', '2024-01-01', 12, 28),
('a1', '2024-02-01', 9, 28),
('a2', '2024-01-01', 10, 28),
('a1', '2024-03-01', 89, 19),
('a1', '2024-10-01', 12, 67),
('a1', '2024-06-01', 12, 28);
Records: 9 Duplicates: 0 Warnings: 0
SELECT * FROM salesdata;
| id | date | stock | sold |
|---|---|---|---|
| a1 | 2024-01-01 | 2 | 28 |
| a1 | 2024-08-01 | 12 | 100 |
| a1 | 2024-04-01 | 12 | 28 |
| a3 | 2024-01-01 | 12 | 28 |
| a1 | 2024-02-01 | 9 | 28 |
| a2 | 2024-01-01 | 10 | 28 |
| a1 | 2024-03-01 | 89 | 19 |
| a1 | 2024-10-01 | 12 | 67 |
| a1 | 2024-06-01 | 12 | 28 |
# Recursive CTE for months
WITH RECURSIVE cte (`id`, `date`, `last_date`) AS (
SELECT `id`, MAX(`date`) - INTERVAL 4 MONTH, MAX(`date`)
FROM salesdata
WHERE `id` = 'a1'
UNION ALL
SELECT `id`, `date` + INTERVAL 1 MONTH, `last_date`
FROM cte
WHERE `date` < `last_date`
)
SELECT * FROM cte;
| id | date | last_date |
|---|---|---|
| a1 | 2024-06-01 | 2024-10-01 |
| a1 | 2024-07-01 | 2024-10-01 |
| a1 | 2024-08-01 | 2024-10-01 |
| a1 | 2024-09-01 | 2024-10-01 |
| a1 | 2024-10-01 | 2024-10-01 |
# Recursive CTE for months
WITH RECURSIVE cte (`id`, `date`, `last_date`) AS (
SELECT `id`, MAX(`date`) - INTERVAL 4 MONTH, MAX(`date`)
FROM salesdata
WHERE `id` = 'a1'
UNION ALL
SELECT `id`, `date` + INTERVAL 1 MONTH, `last_date`
FROM cte
WHERE `date` < `last_date`
)
SELECT cte.id, cte.date, IFNULL(sd.stock, 0) AS stock, IFNULL(sd.sold, 0) AS sold
FROM cte
LEFT JOIN salesdata sd
ON cte.date = sd.date
AND cte.id = sd.id;
| id | date | stock | sold |
|---|---|---|---|
| a1 | 2024-06-01 | 12 | 28 |
| a1 | 2024-07-01 | 0 | 0 |
| a1 | 2024-08-01 | 12 | 100 |
| a1 | 2024-09-01 | 0 | 0 |
| a1 | 2024-10-01 | 12 | 67 |