sql query to solve the following

I have to following table in sql server:

date                 |   status  

2009-01-01 12:00:00      OK
2009-01-01 12:03:00      FAILED
2009-01-01 12:04:00      OK
2009-01-01 12:06:20      OK
2009-01-01 12:07:35      FAILED
2009-01-01 12:07:40      FAILED
2009-01-01 12:20:40      FAILED
2009-01-01 12:25:40      OK

I need the following: starting 2009-01-01 12:00:00, every 10 minute from this date i need to see the number of OK and FAILED.

something like:

INTERVAL                                  FAILED      OK
2009-01-01 12:00:00-2009-01-01 12:15:00    1           2
2009-01-01 12:15:01-2009-01-01 12:30:00    0           1

etc..

what is the best way开发者_运维技巧 to do this in sql?

Ok first of all ..

You mention 10 minutes and the provide an example with 15 minutes.. Additionally you sample data should return different results than what you posted..

solution using Pivot

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

this will return

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

Update after comments

This version will include time intervals that do not have values in the database.. We will need to create a temporary table on the fly ..

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

Using a 10 minute interval now, to show a period without values, returns..

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

There might be an easier way to do it but this works:

--CREATE TABLE temptest
--(
--  date1 DATETIME,
--  stat nvarchar(10)
--)

--INSERT INTO temptest
--VALUES 
--('2009-01-01 12:00:00','OK'),
--('2009-01-01 12:03:00','FAILED'),
--('2009-01-01 12:04:00','OK'),
--('2009-01-01 12:06:20','OK'),
--('2009-01-01 12:07:35','FAILED'),
--('2009-01-01 12:07:40','FAILED'),
--('2009-01-01 12:20:40','FAILED'),
--('2009-01-01 12:25:40','OK')

SELECT
    stat,
    COUNT(1),
    YEAR(date1),
    MONTH(date1),
    DAY(date1),
    DATEPART(hh,date1),
    ROUND(DATEPART(MINUTE,date1)/10,0)
FROM temptest
GROUP BY stat, YEAR(date1), MONTH(date1), DAY(date1), DATEPART(hh,date1), ROUND(DATEPART(MINUTE,date1)/10,0)

Because I don't know your table name, something like this SHOULD work.

DECLARE @startTime DATETIME
DECLARE @endTime DATETIME

SELECT @startTime = '1/1/2010 00:00:00'
SELECT @endTime = GETDATE()

SELECT 
    cast(@startTime as varchar) + ' - ' + cast(@endTime as varchar) as Interval, 
    (select count(1) from [table] where status = 'FAILED') as FAILED, 
(Select count(1) from [table where status = 'OK') as OK
FROM
    [table]
WHERE
    date between @startTime and @endTime

This is using a recursive CTE.

declare @startdate datetime
declare @enddate datetime
declare @interval int

set @startdate = '2009-01-01 12:00:00'
set @enddate = '2009-01-02 12:00:00'
set @interval = 15

;with intervals ( i, d ) AS 
(
    select 1, @startdate 
    union all
    select i+1, DATEADD(MINUTE, (@interval*i), @startdate) from intervals where i < 100
)
select d as 'From', DATEADD(MINUTE, (@interval-1), d) as 'To',
    (select COUNT(*) from yourTable where thedate between d and DATEADD(MINUTE, (@interval-1), d) and thestatus = 'FAILED') as 'FAILED',
    (select COUNT(*) from yourTable where thedate between d and DATEADD(MINUTE, (@interval-1), d) and thestatus = 'OK') as 'OK'
from intervals
option (MAXRECURSION 100)

The output looks like this:

From                    To                      FAILED      OK
----------------------- ----------------------- ----------- -----------
2009-01-01 12:00:00.000 2009-01-01 12:14:00.000 3           3
2009-01-01 12:15:00.000 2009-01-01 12:29:00.000 1           1
2009-01-01 12:30:00.000 2009-01-01 12:44:00.000 0           0
2009-01-01 12:45:00.000 2009-01-01 12:59:00.000 0           0
2009-01-01 13:00:00.000 2009-01-01 13:14:00.000 0           0
2009-01-01 13:15:00.000 2009-01-01 13:29:00.000 0           0
2009-01-01 13:30:00.000 2009-01-01 13:44:00.000 0           0

Please note in your data you have the same number of failed and ok in the time slots.

Another option...

CREATE TABLE #results ( IntervalStart DATETIME, IntervalEnd DATETIME, FailedCount INT, OKCount INT );
DECLARE @EndPoint DATETIME
DECLARE @CurrentPoint DATETIME
DECLARE @PeriodEnd DATETIME

SET @CurrentPoint = '2009-01-01 12:00:00'
SET @EndPoint = '2009-03-01 12:00:00' -- choose any end point, could be today: GETDATE()

WHILE @CurrentPoint < @EndPoint
BEGIN
    SET @PeriodEnd = DATEADD(mi, 10, @CurrentPoint)

    INSERT INTO #results
    SELECT @CurrentPoint, @PeriodEnd, 
       (SELECT COUNT(Status) FROM StatusSource WHERE StatusPoint BETWEEN @CurrentPoint AND @PeriodEnd AND Status = 'FAILED'),
       (SELECT COUNT(Status) FROM StatusSource WHERE StatusPoint BETWEEN @CurrentPoint AND @PeriodEnd AND Status = 'OK')

    SET @CurrentPoint = @PeriodEnd
END

SELECT 
    CAST(@IntervalStart AS VARCHAR(20)) + ' - ' + cast(@IntervalEnd AS VARCHAR(20)) as Interval,
    FailedCount AS FAILED,
    OKCount AS OK
FROM
    #results

DROP TABLE #results

Here's the tally table version.

Set up some dummy data:

/*

CREATE TABLE MyTable
( 
  MyDate DATETIME, 
  Status varchar(10) 
) 

INSERT INTO Mytable VALUES ('2009-01-01 12:00:00','OK')
INSERT INTO Mytable VALUES ('2009-01-01 12:03:00','FAILED')
INSERT INTO Mytable VALUES ('2009-01-01 12:04:00','OK')
INSERT INTO Mytable VALUES ('2009-01-01 12:06:20','OK') 
INSERT INTO Mytable VALUES ('2009-01-01 12:07:35','FAILED')
INSERT INTO Mytable VALUES ('2009-01-01 12:07:40','FAILED') 
INSERT INTO Mytable VALUES ('2009-01-01 12:20:40','FAILED') 
INSERT INTO Mytable VALUES ('2009-01-01 12:25:40','OK') 

*/

Set up values and paramters. I hard-coded everything for 10 minute intervals, but this too could be a paramter.

DECLARE
  @StartAt  datetime
 ,@Through  datetime

SET @StartAt = 'Jan 1, 2009'
SET @Through = getdate()  --  or whenever

And the query. This lists rows only when there is data to list; make it an inner join to also list "time slots" without activity.

;WITH
  --  Itzik Ben-Gan's tally table routine
  Pass0 as (select 1 as C union all select 1), --2 rows
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
  Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
  Tally as (select row_number() over(order by C) as Number from Pass5)

(...look up discussions on "tally tables" or "table of numbers" for the what-and-why's behind this...)

 select
    xx.FromTime
   ,sum(case when mt.Status = 'OK' then 1 else 0 end)     HowManyOk
   ,sum(case when mt.Status = 'Failed' then 1 else 0 end) HowManyFailed
  from (select
           dateadd(mi, (Number-1) * 10, @StartAt) FromTime
          ,dateadd(mi, Number * 10, @StartAt)     ThruTime
         from Tally where Number <= datediff(mi, @StartAt, @Through) /10) xx
   inner join MyTable mt
    on mt.MyDate >= xx.FromTime and mt.MyDate < xx.ThruTime
  group by xx.FromTime

So my question is: of all the methods presented, which scales better as data volume increases? I hope somebody tests this.