sql-server - SQL Server相关问题

Question

我有这件事需要做，一些建议将不胜感激。我有一个包含一些电话的 SQL Server 表。对于每个电话，我都有开始和结束时间。

我需要完成的任务:一个存储过程，在一段时间内(假设以 x 间隔 5 小时)返回已连接调用的数量。

Something like:

Interval                                    Nr of Calls Connected
01-01-2010 12:00:00 - 01-01-2010 12:05:00   30
01-01-2010 12:05:01 - 01-01-2010 12:10:00   10
.............

哪种方法是最快的方法？谢谢您的帮助

Answer 1

这适用于有调用的时间间隔 ...

Declare @datetimestart datetime
Declare @interval int
Set @datetimestart = '2009-01-01 12:00:00'
Set @interval = 5 --in minutes

Select
  [start_interval], [end_interval] , count([start_interval]) as [calls]
From
  (
    Select 
     DateAdd( Minute,Floor(DateDiff(Minute,@datetimestart,[date])/@interval)*@interval
,@datetimestart) , 
    DateAdd( Minute,@interval + Floor(DateDiff(Minute,@datetimestart,[date])/@interval)*@interval
,@datetimestart) 
    From yourTable
  ) As W([start_interval],[end_interval])
group by [start_interval], [end_interval]

这适用于所有时间间隔，无论调用次数如何..

Declare @datetimestart datetime, @datetimeend datetime, @datetimecurrent datetime
Declare @interval int
Set @datetimestart = '2009-01-01 12:00:00'
Set @interval = 10
Set @datetimeend = (Select max([date]) from yourtable)

SET @datetimecurrent = @datetimestart

declare @temp as table ([start_interval] datetime, [end_interval] datetime)

while  @datetimecurrent < @datetimeend
BEGIN
  insert into @temp select (@datetimecurrent), dateAdd( minute, @interval, @datetimecurrent)
  set @datetimecurrent = dateAdd( minute, @interval, @datetimecurrent)
END

Select
  *
From
  (
    Select 
      [start_interval],[end_interval], count(d.[start_time]) 
    From @temp t left join yourtable d on d.[start_time] between t.[start_interval] and t.[end_interval]
  ) As W([start_interval],[end_interval], [calls])