sql query to solve the following

Question

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?

Answer 1

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

Answer 2

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)