MySQL 5.5

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

select version();


version()


5.5.62


CREATE TABLE mytable ( 
ID VARCHAR(20) ,
Name VARCHAR(20) ,
Startdate DATE ,
enddate DATE );

INSERT INTO  mytable  VALUES
('1','robert','2024-01-01','2024-01-31'),
('2','ann','2024-01-13','2024-01-20'),
('3','ken','2024-01-20','2024-01-25'),
('4','marco','2024-01-20','2024-01-30');

Records: 4  Duplicates: 0  Warnings: 0


SELECT * FROM mytable order by ID;


ID
Name
Startdate
enddate


1
robert
2024-01-01
2024-01-31

2
ann
2024-01-13
2024-01-20

3
ken
2024-01-20
2024-01-25

4
marco
2024-01-20
2024-01-30


-- For mysql 8.0
/*
WITH recursive dates AS (
  SELECT MIN(Startdate) as d FROM mytable
  UNION ALL
  SELECT DATE_ADD(d,INTERVAL 1 DAY) FROM dates WHERE d <= (SELECT MAX(enddate) FROM mytable)
)
SELECT m.Name, d.d
FROM dates d
JOIN mytable m ON d.d BETWEEN m.Startdate and m.enddate
WHERE DAYOFWEEK(d) = 1
ORDER BY d.d;
*/
CREATE TEMPORARY TABLE dates(d DATE);
SELECT @Rmin := MIN(Startdate) FROM mytable;
SELECT @Rmax := MAX(enddate) FROM mytable;
INSERT INTO dates VALUES (@Rmin);

SET @row := 0;
INSERT INTO dates
SELECT DATE_ADD(@Rmin,  INTERVAL @row:=@row+1 DAY)
FROM information_schema.columns
WHERE @row <= datediff(@Rmax,@Rmin)-1;

SELECT m.Name, d.d
FROM dates d
JOIN mytable m ON d.d BETWEEN m.Startdate and m.enddate
WHERE DAYOFWEEK(d) = 1
ORDER BY d.d;


@Rmin := MIN(Startdate)


2024-01-01




@Rmax := MAX(enddate)


2024-01-31



Records: 30  Duplicates: 0  Warnings: 0




Name
d


robert
2024-01-07

robert
2024-01-14

ann
2024-01-14

robert
2024-01-21

ken
2024-01-21

marco
2024-01-21

marco
2024-01-28

robert
2024-01-28

ID	Name	Startdate	enddate
1	robert	2024-01-01	2024-01-31
2	ann	2024-01-13	2024-01-20
3	ken	2024-01-20	2024-01-25
4	marco	2024-01-20	2024-01-30

Name	d
robert	2024-01-07
robert	2024-01-14
ann	2024-01-14
robert	2024-01-21
ken	2024-01-21
marco	2024-01-21
marco	2024-01-28
robert	2024-01-28