Split time in intervals of 15

Question

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?

Answer 1

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]) <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&fiddle=b02953ffbc0d70fe1e863a43e63c551f