SQL Server 相关问题 [英] SQL Server related question

问题描述

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

I have this thing that i need to do and some advices will be greatly appreciated. I have a SQL server table with some phone calls.For each phone call i have the start and end time.

我需要完成的是:一个存储过程，它在一段时间内，假设以 x 间隔 5 小时，假设 2 分钟返回连接的呼叫数.

What i need to accomplish: a stored procedure which for a certain period of time, let's say 5 hours at a x interval, lets say 2 minutes returns the number of connected calls.

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
.............

哪种方法最快?感谢您的帮助

Which will be the fastest way to do that? Thank you for your help

推荐答案

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

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])

这篇关于SQL Server 相关问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！