SQL Server 2019 (Linux)

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

-- Create the Events table
CREATE TABLE Events (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(255),
    FromDate DATE,
    ToDate DATE
);

-- Populate the table with two records
INSERT INTO Events (Name, FromDate, ToDate)
VALUES ('Event 1', '2023-11-10', '2023-11-11'),
       ('Event 2', '2023-11-15', '2023-11-17');
2 rows affected

SELECT * FROM Events;


ID
Name
FromDate
ToDate


1
Event 1
2023-11-10
2023-11-11

2
Event 2
2023-11-15
2023-11-17


-- Create a temporary table to store the expanded events
CREATE TABLE #ExpandedEvents (
	ID INT
	,Name NVARCHAR(255)
	,EventDate DATE
	,FromDate DATE
	,ToDate DATE
	);

-- Declare variables for cursor and looping
DECLARE @EventID INT;
DECLARE @EventName NVARCHAR(255);
DECLARE @FromDate DATE;
DECLARE @ToDate DATE;
DECLARE @CurrentDate DATE;

-- Declare a cursor to fetch event data
DECLARE EventCursor CURSOR
FOR
SELECT ID
	,Name
	,FromDate
	,ToDate
FROM Events;

-- Open the cursor
OPEN EventCursor;

-- Initialize the variables for the first row
FETCH NEXT
FROM EventCursor
INTO @EventID
	,@EventName
	,@FromDate
	,@ToDate;

-- Loop through the events
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @CurrentDate = @FromDate;

	-- Expand the date range and insert into the temporary table
	WHILE @CurrentDate <= @ToDate
	BEGIN
		INSERT INTO #ExpandedEvents (
			ID
			,Name
			,EventDate
			,FromDate
			,ToDate
			)
		VALUES (
			@EventID
			,@EventName
			,@CurrentDate
			,@FromDate
			,@ToDate
			);

		-- Increment the current date
		SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
	END;

	-- Fetch the next event
	FETCH NEXT
	FROM EventCursor
	INTO @EventID
		,@EventName
		,@FromDate
		,@ToDate;
END;

-- Close and deallocate the cursor
CLOSE EventCursor;

DEALLOCATE EventCursor;

-- Select the expanded events from the temporary table
SELECT ID
	,Name
	,EventDate
	,FromDate
	,ToDate
FROM #ExpandedEvents
ORDER BY ID
	,EventDate;

-- Drop the temporary table
DROP TABLE #ExpandedEvents;


ID
Name
EventDate
FromDate
ToDate


1
Event 1
2023-11-10
2023-11-10
2023-11-11

1
Event 1
2023-11-11
2023-11-10
2023-11-11

2
Event 2
2023-11-15
2023-11-15
2023-11-17

2
Event 2
2023-11-16
2023-11-15
2023-11-17

2
Event 2
2023-11-17
2023-11-15
2023-11-17

ID	Name	EventDate	FromDate	ToDate
1	Event 1	2023-11-10	2023-11-10	2023-11-11
1	Event 1	2023-11-11	2023-11-10	2023-11-11
2	Event 2	2023-11-15	2023-11-15	2023-11-17
2	Event 2	2023-11-16	2023-11-15	2023-11-17
2	Event 2	2023-11-17	2023-11-15	2023-11-17