диапазон времени и диапазон дат в TSQL

Я хочу написать процедуру на TSQL, которая вычисляет время пиковой нагрузки между заданными диапазонами дат, разделенными на заданное время.

Start time: 10-02-2012 10:00
End time  : 10-02 2012 11:00
time range: every 5 minutes

так будет:

10:00 range 1  -> 5 peak times
10:05 range 2  -> 11 peak times
.
.
.
11:00 range 11 -> 7 peak times

когда временной диапазон задан 30 минут, тогда код рассчитает 2 диапазона

Должен ли я использовать интервал? Как я могу решить эту проблему? Любая помощь?


sql-server tsql sql-server-2008 date-range
person cihadakt    schedule 18.10.2012    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Вы можете определить временные диапазоны таким образом:

declare @StartTime as DateTime = '10-02-2012 10:00'
declare @EndTime as DateTime ='10-02-2012 11:00'
declare @TimeRange as Time = '00:05:00.000'

; with TimeRanges as (
  select @StartTime as StartTime, @StartTime + @TimeRange as EndTime
  union all
  select StartTime + @TimeRange, EndTime + @TimeRange
    from TimeRanges
    where EndTime < @EndTime ) -- Corrected.
  select StartTime, EndTime
    from TimeRanges

Соедините диапазоны с вашими демонстрационными данными, чтобы получить сводку:

declare @StartTime as DateTime = '10-02-2012 10:00'
declare @EndTime as DateTime ='10-02-2012 11:00'
declare @TimeRange as Time = '00:05:00.000'

declare @Samples as Table ( SampleId Int Identity, SampleTime DateTime )
insert into @Samples ( SampleTime ) values
  ( '10-02-2012 9:00' ), ( '10-02-2012 10:00' ), ( '10-02-2012 10:02' ), ( '10-02-2012 10:02' ),
  ( '10-02-2012 10:05' ), ( '10-02-2012 10:20' ), ( '10-02-2012 10:34' ), ( '10-02-2012 11:30' )

; with TimeRanges as (
  select @StartTime as StartTime, @StartTime + @TimeRange as EndTime
  union all
  select StartTime + @TimeRange, EndTime + @TimeRange
    from TimeRanges
    where EndTime < @EndTime ) -- Corrected.
  select StartTime, EndTime, Count( S.SampleId ) as Samples
    from TimeRanges as TR left outer join
      @Samples as S on TR.StartTime <= S.SampleTime and S.SampleTime < TR.EndTime
    group by TR.StartTime, TR.EndTime
person HABO    schedule 18.10.2012
comment
но здесь есть проблема. Когда время окончания 11:00, программа не останавливается в 11:00, последняя строка находится между 11:00 и 11:05? Как мы можем решить эту проблему? - person cihadakt; 19.10.2012
comment
@cihata87 - Извините за это. Я исправил проверки завершения рекурсии. - person HABO; 19.10.2012
comment
HABO У меня возникла проблема, когда я хочу получить ежедневный отчет. 24:00:00.000 не работает и выдает ошибку, 23:59:59 вызывает задержки в моем отчете, как мы можем это исправить? Какие-либо предложения? - person cihadakt; 22.10.2012
comment
@ cihata87 - я немного не понял ваш последний комментарий. Ежедневный отчет будет запускаться от одной даты к другой, например. @StartTime = '10-10-2012 00:00' и @EndDate = '10-11-2012 00:00. Если вы говорите, что хотите отображать дату/время странным образом, вы всегда можете отформатировать его самостоятельно, например. case when EndTime = @EndTime and Cast( @EndTime as Time ) = '00:00' then Convert( VarChar(10), DateAdd( day, -1, EndTime ), 121 ) + ' 23:59:60.000' else Convert( VarChar(23), EndTime, 121 ) end as FunnyTime. - person HABO; 22.10.2012

arrow_upward
0
arrow_downward

Используйте цикл с функцией DATEADD.

http://msdn.microsoft.com/en-us/library/ms186819.aspx

Продолжайте добавлять свой «минутный» интервал, пока полученная дата не будет больше, чем конец.

ИЗМЕНИТЬ

BEGIN
-- setup
DECLARE @start DATETIME
DECLARE @end DATETIME
DECLARE @interval INT
DECLARE @samples TABLE (
    [time] DATETIME
)
SET @start = CAST('10-02-2012 10:00' as DATETIME)
SET @end = CAST('10-02-2012 11:00' as DATETIME)
SET @interval = 5
INSERT INTO @samples VALUES 
    ( '10-02-2012 9:00' ), ( '10-02-2012 10:00' ), ( '10-02-2012 10:02' )
    , ( '10-02-2012 10:02' ), ( '10-02-2012 10:05' ), ( '10-02-2012 10:20' )
    , ( '10-02-2012 10:34' ), ( '10-02-2012 11:30' )
-- make the ranges
DECLARE @ranges TABLE (
    [start] datetime
    ,[end] datetime
)
DECLARE @tmp DATETIME
SET @tmp = DATEADD(minute, @interval, @start)
IF @tmp > @end BEGIN SET @tmp = @end END
WHILE @start < @end
    BEGIN
    INSERT INTO @ranges VALUES (@start, @tmp)
    SET @start = @tmp
    SET @tmp = DATEADD(minute, @interval, @start)
    IF @tmp > @end BEGIN SET @tmp = @end END
    END
-- execute the query
SELECT r.[start], r.[end], count(s.[time]) [count]
FROM @ranges r
    LEFT JOIN @samples s 
        ON r.[start] <= s.[time] AND r.[end] > s.[time]
GROUP BY r.[start], r.[end]
END

Я предлагал использовать простой цикл while для генерации нужных вам диапазонов. Для меня это более прямолинейно/легко понять, чем решение для рекурсивных запросов CTE, хотя, по общему признанию, менее элегантно.

person Louis Ricci    schedule 18.10.2012
comment
Вы можете быть более конкретным? как я могу использовать datepart для этой проблемы? - person cihadakt; 19.10.2012