SQL Server – How to Split Time in Intervals of 15 Minutes

This is my table with data:

I want to select it as:

Where 0=office, 1=home,2 = telemedice

The time will always be a multiple of 5.

How can i do that?

I have tried this:


WITH CTE
AS (SELECT fromDate AS [Time],
1 AS RLevel,
id,
CASE appointMode
WHEN 0 THEN
'office'
WHEN 1 THEN
'home'
ELSE
'tele'
END AS mode
FROM tbl_provider_available_schedule
UNION ALL
SELECT DATEADD(MINUTE, 15, [Time]),
RLevel + 1,
id,
CTE.mode
FROM CTE
WHERE RLevel < 4)
SELECT id,
[Time],
CTE.RLevel,
mode
FROM CTE
WHERE [Time] <=
(
SELECT MAX(toDate) FROM dbo.tbl_provider_available_schedule
)
ORDER BY [Time];


Result that i get is:

My table script is:

CREATE TABLE [dbo].[tbl_provider_available_schedule]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[practiceid] [int] NULL,
[providerid] [int] NULL,
[providerlocationid] [int] NULL,
[fromDate] [datetime] NULL,
[toDate] [datetime] NULL,
[appointMode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_provider_available_schedule] ADD CONSTRAINT [PK_tbl_provider_available_schedule] PRIMARY KEY CLUSTERED ([id]) ON [PRIMARY]
GO

My data script is:

INSERT INTO dbo.tbl_provider_available_schedule
(
practiceid,
providerid,
providerlocationid,
fromDate,
toDate,
appointMode
)
VALUES
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T11:45:00', 0),
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T12:00:00', 2),
(1, 1, 2, N'2019-09-19T11:30:00', N'2019-09-19T11:45:00', 1);

How can i achieve the above mentioned required results?

I have tried this:


WITH CTE
AS (SELECT fromDate AS [Time],
1 AS RLevel,
id,
CASE appointMode
WHEN 0 THEN
'office'
WHEN 1 THEN
'home'
ELSE
'tele'
END AS mode
FROM tbl_provider_available_schedule
UNION ALL
SELECT DATEADD(MINUTE, 15, [Time]),
RLevel + 1,
id,
CTE.mode
FROM CTE
WHERE RLevel < 4)
SELECT id,
[Time],
CTE.RLevel,
mode
FROM CTE
WHERE [Time] <=
(
SELECT MAX(toDate) FROM dbo.tbl_provider_available_schedule
)
ORDER BY [Time];


Result that i get is:

My table script is:

CREATE TABLE [dbo].[tbl_provider_available_schedule]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[practiceid] [int] NULL,
[providerid] [int] NULL,
[providerlocationid] [int] NULL,
[fromDate] [datetime] NULL,
[toDate] [datetime] NULL,
[appointMode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_provider_available_schedule] ADD CONSTRAINT [PK_tbl_provider_available_schedule] PRIMARY KEY CLUSTERED ([id]) ON [PRIMARY]
GO

My data script is:

INSERT INTO dbo.tbl_provider_available_schedule
(
practiceid,
providerid,
providerlocationid,
fromDate,
toDate,
appointMode
)
VALUES
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T11:45:00', 0),
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T12:00:00', 2),
(1, 1, 2, N'2019-09-19T11:30:00', N'2019-09-19T11:45:00', 1);

How can i achieve the above mentioned required results?