sql query to solve the following

Question

Hi All,

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

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

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)

Answer 3

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.

Answer 4

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

Answer 5

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.