By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.36 |
CREATE TABLE items (
id INT PRIMARY KEY AUTO_INCREMENT,
dateadded DATE,
dateremoved DATE,
serialnumber VARCHAR(40));
INSERT INTO items(dateadded,dateremoved,serialnumber) VALUES
('2022-01-01',NULL,'ABC1'),
('2022-01-02','2022-01-04','ABC2'),
('2022-01-03',NULL,'ABC3'),
('2022-01-04','2022-01-08','ABC4'),
('2022-01-03',NULL,'DEF1'),
('2022-01-04','2022-01-04','DEF2'),
('2022-01-05',NULL,'DEFC3'),
('2022-01-06','2022-01-08','DEF4');
Records: 8 Duplicates: 0 Warnings: 0
SELECT *
FROM items;
| id | dateadded | dateremoved | serialnumber |
|---|---|---|---|
| 1 | 2022-01-01 | null | ABC1 |
| 2 | 2022-01-02 | 2022-01-04 | ABC2 |
| 3 | 2022-01-03 | null | ABC3 |
| 4 | 2022-01-04 | 2022-01-08 | ABC4 |
| 5 | 2022-01-03 | null | DEF1 |
| 6 | 2022-01-04 | 2022-01-04 | DEF2 |
| 7 | 2022-01-05 | null | DEFC3 |
| 8 | 2022-01-06 | 2022-01-08 | DEF4 |
WITH RECURSIVE dates AS(
SELECT
min(dateadded) AS d,
0 as x
FROM items
UNION ALL
SELECT DATE_ADD(d,INTERVAL 1 DAY),x+1
FROM dates
WHERE d<=(SELECT MAX(dateadded) FROM items)
)
SELECT
d,
serialnumber
FROM dates
LEFT JOIN items ON d BETWEEN items.dateadded
AND COALESCE(items.dateremoved,'2099-12-31')
ORDER BY 1,2
| d | serialnumber |
|---|---|
| 2022-01-01 | ABC1 |
| 2022-01-02 | ABC1 |
| 2022-01-02 | ABC2 |
| 2022-01-03 | ABC1 |
| 2022-01-03 | ABC2 |
| 2022-01-03 | ABC3 |
| 2022-01-03 | DEF1 |
| 2022-01-04 | ABC1 |
| 2022-01-04 | ABC2 |
| 2022-01-04 | ABC3 |
| 2022-01-04 | ABC4 |
| 2022-01-04 | DEF1 |
| 2022-01-04 | DEF2 |
| 2022-01-05 | ABC1 |
| 2022-01-05 | ABC3 |
| 2022-01-05 | ABC4 |
| 2022-01-05 | DEF1 |
| 2022-01-05 | DEFC3 |
| 2022-01-06 | ABC1 |
| 2022-01-06 | ABC3 |
| 2022-01-06 | ABC4 |
| 2022-01-06 | DEF1 |
| 2022-01-06 | DEF4 |
| 2022-01-06 | DEFC3 |
| 2022-01-07 | ABC1 |
| 2022-01-07 | ABC3 |
| 2022-01-07 | ABC4 |
| 2022-01-07 | DEF1 |
| 2022-01-07 | DEF4 |
| 2022-01-07 | DEFC3 |