好吧,首先..
您提到 10 分钟,并提供了 15 分钟的示例。此外,您的示例数据应该返回与您发布的结果不同的结果。
solution使用枢轴
Declare @datetimestart datetime
Declare @interval int
Set @datetimestart = '2009-01-01 12:00:00'
Set @interval = 15
Select
*
From
(
Select
DateAdd( Minute,Floor(DateDiff(Minute,@datetimestart,[date])/@interval)*@interval
,@datetimestart),
DateAdd( Minute,@interval + Floor(DateDiff(Minute,@datetimestart,[date])/@interval)*@interval
,@datetimestart)
, status
From dtest
) As W([from],[to], status)
Pivot (Count(status) For status In ([ok],[failed])) p
这将返回
From To Ok Failed
2009-01-01 12:00:00.000 2009-01-01 12:15:00.000 3 3
2009-01-01 12:15:00.000 2009-01-01 12:30:00.000 1 0
评论后更新
此版本将包括数据库中没有值的时间间隔。
我们需要动态创建一个临时表..
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 dtest)
SET @datetimecurrent = @datetimestart
declare @temp as table ([from] datetime,[to] datetime)
while @datetimecurrent < @datetimeend
BEGIN
insert into @temp select (@datetimecurrent), dateAdd( minute, @interval, @datetimecurrent)
set @datetimecurrent = dateAdd( minute, @interval, @datetimecurrent)
END
Select
*
From
(
Select
[from],[to], status
From @temp t left join dtest d on d.[date] between t.[from] and t.[to]
) As W([from],[to], status)
Pivot (Count(status) For status In ([ok],[failed])) p
现在使用 10 分钟间隔来显示没有值的时间段,返回..
From To Ok Failed
2009-01-01 12:00:00.000 2009-01-01 12:10:00.000 3 3
2009-01-01 12:10:00.000 2009-01-01 12:20:00.000 0 0
2009-01-01 12:20:00.000 2009-01-01 12:30:00.000 1 0