主页 / dba / 问题 / 129518
Accepted
AA.SC
Asked: 2016-02-18 03:05:06 +0800 CST

为时间表生成时间段的有效方法

  • 772

我有一个名为#MySchedulecolumnsStartTime和EndTime的表duration。

我需要的是一个查询，以根据持续时间列在这些 StartTime 和 EndTime 之间获取 MySchedule 中每一行的所有时间槽。

我的日程表：

create table #MySchedule
(
startTime datetime, 
endtime datetime,
duration int
)

insert into #MySchedule
select '1/3/15 10:00 AM' startTime ,'1/3/15 4:00 PM' endTime ,15 duration 
union all 
select '1/4/15 10:00 AM' startTime ,'1/4/15 2:00 PM' endTime ,30 duration 
union all 
select '1/5/15 10:00 AM' startTime ,'1/5/15 4:00 PM' endTime ,45 duration

预期输出：

    Slots   StartTime           EndTime
    1       '1/3/15 10:00 AM'   '1/3/15 10:15 AM'
    2       '1/3/15 10:15 AM'   '1/3/15 10:30 AM'
    3       '1/3/15 10:30 AM'   '1/3/15 10:45 AM'
    4       '1/3/15 10:45 AM'   '1/3/15 11:00 AM'
    5       '1/3/15 11:00 AM'   '1/3/15 11:15 AM'
    .       ...                 ...
    .       '1/3/15 3:45 PM'    '1/3/15 4:00 PM'
    ---------------------------------------------
    .       '1/4/15 10:00 AM'   '1/4/15 10:30 AM'
    .       '1/4/15 10:30 AM'   '1/4/15 11:00 AM'
    .       ...                 ...
    .       '1/4/15 13:30 AM'   '1/4/15 14:00 PM'
    ---------------------------------------------
    .       '1/5/15 10:00 AM'   '1/5/15 10:45 AM'
    .       '1/5/15 10:45 AM'   '1/5/15 11:30 AM'
    .       ...                 ...
    .       '1/5/15 15:15 AM'   '1/5/15 16:00 PM'
sql-server sql-server-2008

1 个回答

  1. Best Answer

    这将适用于多达 100 个间隔。它可以通过添加另一个轻松扩展到 1000 CROSS JOIN inc，依此类推 10.000 ...

    它假设持续时间以分钟为单位。

    • CTE inc 创建一个有 10 行的派生表，其中有 10 个 1。
    • CTE 列出CROSS JOINCTE inc 两次并创建 10*10 行。
    • 它还使用ROW_NUMBER().
    • 然后将这些数字添加到@starttime，直到达到@endtime。

    询问：

    DECLARE @schedule TABLE(Id int identity(0, 1), StartTime datetime2, EndTime datetime2, Duration int);
INSERT INTO @schedule(StartTime, EndTime, Duration) VALUES
    ('20150103 10:00', '20150103 16:00', 15)
    , ('20150104 10:00', '20150104 14:00', 30)
    , ('20150105 10:00', '20150105 16:00', 45);


WITH inc AS (
    SELECT n FROM (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as i(n)
), list AS(
    SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT 1)) 
    FROM inc i10
    CROSS JOIN inc i100
    --CROSS JOIN inc i1000
)
SELECT s.id, l.n
    , DATEADD(minute, s.duration*(n-1), s.StartTime) as StartTime
    , DATEADD(minute, s.duration*n, s.StartTime) as EndTime
    , DATEDIFF(minute, s.StartTime, s.EndTime)/s.Duration
FROM @schedule s
INNER JOIN list l ON
      l.n <= DATEDIFF(minute, s.StartTime, s.EndTime)/s.Duration
ORDER BY s.Id, l.n

    输出：

    StartTime                   | EndTime
2015-01-03 10:00:00.0000000 | 2015-01-03 10:15:00.0000000
2015-01-03 10:15:00.0000000 | 2015-01-03 10:30:00.0000000
...                         | ...
2015-01-03 15:30:00.0000000 | 2015-01-03 15:45:00.0000000
2015-01-03 15:45:00.0000000 | 2015-01-03 16:00:00.0000000
----------------------------|----------------------------    
2015-01-04 10:00:00.0000000 | 2015-01-04 10:30:00.0000000
2015-01-04 10:30:00.0000000 | 2015-01-04 11:00:00.0000000
...                         | ...
2015-01-04 13:00:00.0000000 | 2015-01-04 13:30:00.0000000
2015-01-04 13:30:00.0000000 | 2015-01-04 14:00:00.0000000
----------------------------|----------------------------    
2015-01-05 10:00:00.0000000 | 2015-01-05 10:45:00.0000000
2015-01-05 10:45:00.0000000 | 2015-01-05 11:30:00.0000000
...                         | ...
2015-01-05 14:30:00.0000000 | 2015-01-05 15:15:00.0000000
2015-01-05 15:15:00.0000000 | 2015-01-05 16:00:00.0000000
    • 1

相关问题