sql-server - 在 SQL Server 中展平计划数据

Question

我一遍又一遍地搜索，但就是不知道如何表达。如果有人问过这个，请原谅我。

我有需要“展平”的计划数据。它看起来像这样:

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

我需要它看起来像下面这样(基本上去除重叠并创建连续的段):

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

我似乎找不到任何明智的方法来做到这一点。有什么想法吗？

Answer 1

这看起来非常复杂，因为我还必须在 SQL 中进行计数，但是，我建议为此创建一个持久对象。此外，由于您希望您的时间重叠，我必须调整最终 SELECT 的值。 .无论如何，这似乎有效:

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