Flattening schedule data in SQL server

Question

I have searched over and over again, but just don't know how to word it. Please forgive me if this has been asked.

I have schedule data that I need to "flatten". It looks like this :

DATE  START   END   CODE
10/1  0700    1530  WORK    -- encompasses the entire shift of the employee
10/1  1015    1030  BREAK
10/1  1200    1230  LUNCH
10/1  1400    1415  BREAK

I need it to look like the following (basically removing overlaps and creating contiguous segments):

DATE  START   END   CODE
10/1  0700    1015  WORK
10/1  1015    1030  BREAK
10/1  1030    1200  WORK
10/1  1200    1230  LUNCH
10/1  1230    1400  WORK
10/1  1400    1415  BREAK
10/1  1415    1530  WORK

I cannot seem to find any sensible way to do this. Any thoughts?

Answer 1

This looks pretty complex as it is, due to me having to make the tally in the SQL as well, however, I suggest making a persisted object for this. Also, as you want your times to overlap, I have to adjust the values the final SELECT. Anyway, this appears to work:

CREATE TABLE dbo.YourTable ([Date] date,
                            [Start] char(4),
                            [End] char(4),
                            Code varchar(5));
GO

INSERT INTO dbo.YourTable (Date,
                           Start,
                           [End],
                           Code)
VALUES ('20190110', '0700', '1530', 'WORK'),
       ('20190110', '1015', '1030', 'BREAK'),
       ('20190110', '1200', '1230', 'LUNCH'),
       ('20190110', '1400', '1415', 'BREAK');
GO
--Create a Time Table on the fly
--First some NULL values
WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
--Now a Tally
Tally AS(
    SELECT TOP (1440) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3, N N4),
--And finally some formatting cause
TimeTable AS(
    SELECT REPLACE(CONVERT(varchar(5),DATEADD(MINUTE,T.I,'00:00'),108),':','') AS [Time]
    FROM Tally T),
--Put into Groups
Grps AS(
    SELECT YT1.Date,
           TT.Time,
           ISNULL(YT2.Code,YT1.Code) AS Code,
           ROW_NUMBER() OVER (PARTITION BY YT1.[date] ORDER BY TT.[Time]) -
           ROW_NUMBER() OVER (PARTITION BY YT1.[date],ISNULL(YT2.Code,YT1.Code) ORDER BY TT.[Time]) AS Grp,
           MIN(YT1.[Start]) OVER (PARTITION BY YT1.[date]) AS DayStart,
           MAX(YT1.[End]) OVER (PARTITION BY YT1.[date]) AS DayEnd
    FROM dbo.YourTable YT1
         JOIN TimeTable TT ON YT1.[Start] <= TT.Time
                          AND YT1.[End] >= TT.Time
         LEFT JOIN dbo.YourTable YT2 ON YT1.Date = YT2.Date
                                   AND YT2.[Start] <= TT.Time
                                   AND YT2.[End] >= TT.Time
                                   AND YT2.Code != 'Work'
    WHERE YT1.Code = 'WORK')
SELECT G.[Date],
       MIN([Time]) + CASE WHEN G.Code = 'Work' AND MIN(Time) != G.DayStart THEN -1 ELSE 0 END AS StartTime,
       MAX([Time]) + CASE WHEN G.Code = 'Work' AND MAX(Time) != G.DayEnd THEN 1 ELSE 0 END AS EndTime,
       G.Code
FROM Grps G
GROUP BY G.Date,
         G.Code,
         G.Grp,
         G.DayStart,
         G.DayEnd
ORDER BY G.Date,
         StartTime;


GO

DROP TABLE dbo.YourTable;

DB<>Fiddle

Answer 2

Here is a method that first builds a list of all the times, using a UNION for the start and end times.

Then in a second CTE uses LEAD to calculate the end time from the next record.

WITH CTE1 AS
(
    SELECT [date], [start], [code]
    FROM timeregistration
    UNION
    SELECT [date], [end], 'WORK'
    FROM timeregistration
),
CTE2 AS
(
    SELECT [date], [start], [code],
    LEAD([start]) OVER (PARTITION BY [date] ORDER BY [start]) AS [end]
    FROM CTE1
)
SELECT [date], [start], [end], [code]
FROM CTE2
WHERE [end] IS NOT NULL
ORDER BY [date], [start];

A test on rextester here