Split time in intervals of 15 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 &lt; 4)
SELECT id,
       [Time],
       CTE.RLevel,
       mode
FROM CTE
WHERE [Time] &lt;=
(
    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 &lt; 4)
SELECT id,
       [Time],
       CTE.RLevel,
       mode
FROM CTE
WHERE [Time] &lt;=
(
    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?
Hello I create a temporary table variable type to not create the table

DECLARE @tbl_provider_available_schedule TABLE
    (
    [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
    );
    INSERT INTO @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);


    WITH CTE
    AS (SELECT fromDate AS [Time],
               1 AS RLevel,
               id,
               toDate,
               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,
               toDate,
               CTE.mode
        FROM CTE
        WHERE DATEADD(MINUTE, 15, [Time]) &lt;toDate--We tell you not to miss the end date
        )
    SELECT id,
           [Time] AS StartDate,
           DATEADD(MINUTE, 15, [Time])  as EndDate,--add 15 minutes more
    CASE WHEN mode='office' THEN 1 ELSE 0 END AS Office,
    CASE WHEN mode='tele' THEN 1 ELSE 0 END AS tele,
    CASE WHEN mode='home' THEN 1 ELSE 0 END AS home
    FROM CTE
    ORDER BY id;


analyze it and you commit me.

PD:The final result is different because in your insert there is more data, notice that by the ID it looks like it was.

Demostration:

https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=b02953ffbc0d70fe1e863a43e63c551f