Flattening schedule data in SQL server
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
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