Início / dba / Perguntas / 129518
Accepted
AA.SC
Asked: 2016-02-18 03:05:06 +0800 CST

Maneira eficiente de gerar Time Slots para um cronograma

  • 772

Eu tenho uma tabela nomeada com #MySchedulecolunas StartTimee .EndTimeduration

O que eu preciso é de uma consulta para obter todos os Slots de tempo para cada linha em MySchedule entre esses StartTime e EndTime com base na coluna de duração.

Tabela 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

Saída esperada:

    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 respostas

  1. Best Answer

    Isso funcionará para até 100 intervalos. Pode ser facilmente expandido para 1000 adicionando outro CROSS JOIN inc, e assim por diante para 10.000 ...

    Supõe que a duração é em minutos.

    • CTE inc cria uma tabela derivada com 10 linhas com dez 1.
    • Lista CROSS JOINCTE CTE aumenta duas vezes e cria 10*10 linhas.
    • Também cria uma lista de números de 1 a 100 (ou 1000...) com ROW_NUMBER().
    • Esses números são adicionados a @starttime até chegar a @endtime.

    Consulta:

    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

    Resultado:

    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

relate perguntas