sql-server 按小时拆分日期/时间数据并将日期/时间范围展开到行中

kdfy810k 于 2022-10-31 发布在其他

关注(0)|答案(2)|浏览(181)

我正在尝试使用SQL Server将日期/时间数据的范围扩展到多行中。例如，我的数据如下所示

Date           StartTime    EndTime     EmployeeID     ShiftType
10/1/2019     8:30:00AM    4:57:00PM     52148          Shift
10/2/2019     9:00:00AM    5:24:00PM     72156          Shift
10/2/2019     8:27:00AM    4:40:00PM     59232          Shift

我想将日期和时间的范围按小时展开到多行中。它将如下所示：

Date           StartTime    EndTime     EmployeeID     ShiftType
10/1/2019     8:30:00AM    9:00:00PM     52148          Shift
10/1/2019     9:00:00AM    10:00:00PM     52148          Shift
10/1/2019     10:00:00AM   11:00:00PM     52148          Shift
10/1/2019     11:00:00AM   12:00:00PM     52148          Shift
10/1/2019     12:00:00AM   1:00:00PM     52148          Shift
10/1/2019     1:00:00AM    2:00:00PM     52148          Shift
10/1/2019     2:00:00AM    3:00:00PM     52148          Shift
10/1/2019     3:00:00AM    4:00:00PM     52148          Shift
10/1/2019     4:30:00AM    4:57:00PM     52148          Shift
10/2/2019     9:00:00AM    10:00:00PM     72156          Shift
10/2/2019     10:00:00AM   11:00:00PM     72156          Shift
                  .......

如果开始时间是8：20，我想为前40分钟创建一个单独的行，这样它就可以是8：20 - 9：00，然后是9：00-10：同样的事情也适用于结束时间。我累了这样的东西，但显然它是行不通的，因为我想基于一个小时的单位来分割它们。

Declare @StartDate DATETIME = '2016-09-26 00:00:00.000';

With SampleDateTable AS 
  (
    SELECT @StartDate AS myDate
    UNION ALL
    SELECT DATEADD(Day,1,myDate)
    FROM Sheet1$
    WHERE DATEADD(Day,1,myDate) <=  GETDATE()
)
SELECT 
    EmployeeID,
    a.myDate,
FROM SampleDateTable a
     INNER JOIN 
      (
        SELECT EmployeeID, MIN(StartTime) MinStartTime
        FROM Sheet1$
        GROUP BY EmployeeID
      ) EachEmployee ON 
        a.MyDate >= EachEmployee.MinStartTime
     LEFT JOIN 
    Sheet1$ S ON
        EachEmployee.EmployeeID = S.EmployeeID AND
        a.myDate >= S.StartDate AND
        a.myDate <= ISNULL(S.EndDate, GETDATE())
ORDER BY EachEmployee.EmployeeID DESC, a.MyDate
OPTION (MAXRECURSION 0)

我会很感激你的帮助谢谢。

sql-server

来源：https://stackoverflow.com/questions/64656386/split-date-time-data-by-hour-and-expand-a-range-of-date-time-into-rows

2条答案

按热度按时间

7cwmlq891#

下面是一个使用临时计数表的选项

示例

Declare @YourTable Table ([Date] date,[StartTime] time,[EndTime] time,[EmployeeID] int,[ShiftType] varchar(50))  
Insert Into @YourTable Values 
 ('10/1/2019','8:30:00AM','4:57:00PM',52148,'Shift')
,('10/2/2019','9:00:00AM','5:24:00PM',72156,'Shift')
,('10/2/2019','8:27:00AM','4:40:00PM',59232,'Shift')

Select Date
      ,StartTime = case when N=datepart(hour,StartTime) then StartTime else TimeFromParts(N,0,0,0,0) end
      ,EndTime   = case when N=datepart(hour,EndTime)   then EndTime   else TimeFromParts(N+1,0,0,0,0) end
      ,EmployeeID
      ,ShiftType
 From  @YourTable A
 Join ( values (0),(1),(2),(3),(4),(5),(6)
              ,(7),(8),(9),(10),(11),(12),(13)
              ,(14),(15),(16),(17),(18),(19),(20)
              ,(21),(22),(23)
       ) B(N)
  on  N between datepart(hour,StartTime) and datepart(hour,EndTime)

退货

Date        StartTime           EndTime             EmployeeID  ShiftType
2019-10-01  08:30:00.0000000    09:00:00.0000000    52148   Shift
2019-10-01  09:00:00.0000000    10:00:00.0000000    52148   Shift
2019-10-01  10:00:00.0000000    11:00:00.0000000    52148   Shift
2019-10-01  11:00:00.0000000    12:00:00.0000000    52148   Shift
2019-10-01  12:00:00.0000000    13:00:00.0000000    52148   Shift
2019-10-01  13:00:00.0000000    14:00:00.0000000    52148   Shift
2019-10-01  14:00:00.0000000    15:00:00.0000000    52148   Shift
2019-10-01  15:00:00.0000000    16:00:00.0000000    52148   Shift
2019-10-01  16:00:00.0000000    16:57:00.0000000    52148   Shift
2019-10-02  09:00:00.0000000    10:00:00.0000000    72156   Shift
2019-10-02  10:00:00.0000000    11:00:00.0000000    72156   Shift
2019-10-02  11:00:00.0000000    12:00:00.0000000    72156   Shift
2019-10-02  12:00:00.0000000    13:00:00.0000000    72156   Shift
2019-10-02  13:00:00.0000000    14:00:00.0000000    72156   Shift
2019-10-02  14:00:00.0000000    15:00:00.0000000    72156   Shift
2019-10-02  15:00:00.0000000    16:00:00.0000000    72156   Shift
2019-10-02  16:00:00.0000000    17:00:00.0000000    72156   Shift
2019-10-02  17:00:00.0000000    17:24:00.0000000    72156   Shift
2019-10-02  08:27:00.0000000    09:00:00.0000000    59232   Shift
2019-10-02  09:00:00.0000000    10:00:00.0000000    59232   Shift
2019-10-02  10:00:00.0000000    11:00:00.0000000    59232   Shift
2019-10-02  11:00:00.0000000    12:00:00.0000000    59232   Shift
2019-10-02  12:00:00.0000000    13:00:00.0000000    59232   Shift
2019-10-02  13:00:00.0000000    14:00:00.0000000    59232   Shift
2019-10-02  14:00:00.0000000    15:00:00.0000000    59232   Shift
2019-10-02  15:00:00.0000000    16:00:00.0000000    59232   Shift
2019-10-02  16:00:00.0000000    16:40:00.0000000    59232   Shift

赞(0）回复(0）举报 2022-10-31

4xrmg8kj2#

对于递归查询，您应该从示例数据开始，然后从那里进行调整。
在递归查询中，我们添加了一个额外的列：下一个小时的边界，也就是NextTime。例如，对于StartTime 8：27，下一个小时的边界是9：00。在递归时，我们将其作为下一个StartTime，并重复执行，直到达到EndTime。
在结果查询中，我们隐藏了NextTime列，但选择NextTime和EndTime中较早的一个作为结果行的EndTime。由于递归不能提供良好顺序的数据，我们还需要按StartTime进行排序。

WITH hourly AS (
  SELECT Date, EmployeeID, ShiftType, EndTime, StartTime
       , TIMEFROMPARTS(DATEPART(hh, StartTime) + 1,
                       0, 0, 0, 0 ) AS NextTime
    FROM SampleDateTable
  UNION ALL
  SELECT Date, EmployeeID, ShiftType, EndTime, NextTime
       , TIMEFROMPARTS(DATEPART(hh, NextTime) + 1,
                       0, 0, 0, 0 ) AS NextTime
    FROM hourly
   WHERE NextTime < EndTime
)
SELECT Date, StartTime
     , CASE WHEN NextTime < EndTime THEN NextTime ELSE EndTime END AS EndTime
     , EmployeeID, ShiftType
  FROM hourly
ORDER BY Date, EmployeeID DESC, StartTime

有关工作示例，请参阅SQL Fiddle。

结构描述 *

CREATE TABLE SampleDateTable (
  Date        date        NOT NULL,
  StartTime   time(0)     NOT NULL,
  EndTime     time(0)     NOT NULL,
  EmployeeID  int         NOT NULL,
  ShiftType   varchar(10) NOT NULL
);

INSERT INTO SampleDateTable VALUES
( '10/1/2019', '8:30:00AM', '4:57:00PM', 52148, 'Shift' ),
( '10/2/2019', '9:00:00AM', '5:24:00PM', 72156, 'Shift' ),
( '10/2/2019', '8:27:00AM', '4:40:00PM', 59232, 'Shift' );

输出 *

Date        StartTime  EndTime   EmployeeID  ShiftType
2019-10-01  08:30:00   09:00:00  52148       Shift
2019-10-01  09:00:00   10:00:00  52148       Shift
2019-10-01  10:00:00   11:00:00  52148       Shift
2019-10-01  11:00:00   12:00:00  52148       Shift
2019-10-01  12:00:00   13:00:00  52148       Shift
2019-10-01  13:00:00   14:00:00  52148       Shift
2019-10-01  14:00:00   15:00:00  52148       Shift
2019-10-01  15:00:00   16:00:00  52148       Shift
2019-10-01  16:00:00   16:57:00  52148       Shift
2019-10-02  09:00:00   10:00:00  72156       Shift
2019-10-02  10:00:00   11:00:00  72156       Shift
2019-10-02  11:00:00   12:00:00  72156       Shift
2019-10-02  12:00:00   13:00:00  72156       Shift
2019-10-02  13:00:00   14:00:00  72156       Shift
2019-10-02  14:00:00   15:00:00  72156       Shift
2019-10-02  15:00:00   16:00:00  72156       Shift
2019-10-02  16:00:00   17:00:00  72156       Shift
2019-10-02  17:00:00   17:24:00  72156       Shift
2019-10-02  08:27:00   09:00:00  59232       Shift
2019-10-02  09:00:00   10:00:00  59232       Shift
2019-10-02  10:00:00   11:00:00  59232       Shift
2019-10-02  11:00:00   12:00:00  59232       Shift
2019-10-02  12:00:00   13:00:00  59232       Shift
2019-10-02  13:00:00   14:00:00  59232       Shift
2019-10-02  14:00:00   15:00:00  59232       Shift
2019-10-02  15:00:00   16:00:00  59232       Shift
2019-10-02  16:00:00   16:40:00  59232       Shift

赞(0）回复(0）举报 2022-10-31

我来回答

sql-server 按小时拆分日期/时间数据并将日期/时间范围展开到行中

2条答案

相关问题

热门标签

最新问答