CODEWITHSUNDEEP
sqlsql-server
NewPy
NewPy

Reputation: 663

Counting number using numbers table with 15 minutes interval

What is the most efficient way to count the number of occurrences according? I found the numbers table is the most efficient way to generate time sequence data based on start time and end time. I have create a number table starts from 0 to 100,000.

I have generate time sequence table as following:

Declare @Start datetime = '2018-01-01 00:00:00.000',@End datetime ='2018-02-01 00:00:00.000';

    SELECT
        DATEADD(MINUTE,Number*15,@Start) StartTime,
        [Number],
        DATEADD(MINUTE,(Number+1)*15,@Start) EndTime
    FROM dbo.Numbers 
    Where (Number+1)*15<=DATEDIFF(MINUTE,@Start,@End)
    Order By Number;

I have table of data like:

Time                      ID
2018-01-01 00:00:01.000   1
2018-01-01 00:00:02.000   1
2018-01-01 00:15:00.000   124
2018-01-01 00:28:00.000   341
2018-01-01 00:26:00.000   111
2018-01-01 01:02:00.000   162
2018-01-01 04:09:00.000   110
2018-01-01 05:09:00.152   398
2018-01-01 08:12:00.000   902
2018-01-01 12:45:00.000   009
2018-01-01 13:23:00.000   000
2018-01-01 15:37:00.000   187

How can I count time based on 15 minutes interval?

Upvotes: 1

Views: 159

Answers (2)

Thomas N
Thomas N

Reputation: 633

How about this. Just a normal group by so you can put in HAVING, SUM, AVERAGE etc too if you want. Run top section once:

 create table TestTable 
 (
 Time datetime,
 ID int
 )
GO

insert into TestTable values('2018-01-01 00:00:01.000' ,  1)
insert into TestTable values('2018-01-01 00:00:02.000'  , 1)
insert into TestTable values('2018-01-01 00:15:00.000'   ,124)
insert into TestTable values('2018-01-01 00:28:00.000'   ,341)
insert into TestTable values('2018-01-01 00:26:00.000'   ,111)
insert into TestTable values('2018-01-01 01:02:00.000'   ,162)
GO

  CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float) RETURNS datetime
AS
BEGIN
    DECLARE @RoundedTime smalldatetime, @Multiplier float

    SET @Multiplier = 24.0 / @RoundTo

    SET @RoundedTime= ROUND(CAST(CAST(CONVERT(varchar, @Time, 121) AS datetime) AS float) * @Multiplier, 0) / @Multiplier

    RETURN @RoundedTime
END
GO

Then the actual working:

    DECLARE @startDate DATETime
    DECLARE @endDate DATETime

    SET @startDate = '2018-01-01'
    SET @endDate = GETDATE()

    DECLARE @dateAxis TABLE
    (
        dt DATETime
    )

    DECLARE @currentDate DATETime = @startDate

    WHILE @currentDate <= @endDate
    BEGIN
        INSERT INTO @dateAxis 
            SELECT @currentDate 

        SET @currentDate = DATEADD(Minute, 15, @currentDate)

    END

-- axis table
--select * from @dateAxis


SELECT 
  dt AS joinDt,
  dataset.MyCount
FROM
@dateAxis axis
LEFT JOIN
(
SELECT
 dbo.RoundTime([Time], 0.5) AS joinDt,
count(*) AS MyCount
FROM 
/*Your table here*/
TestTable tt
group by
 dbo.RoundTime([Time], 0.5)
) dataset
ON dataset.joinDt = axis.dt
ORDER BY 
axis.dt

Upvotes: 0

D-Shih
D-Shih

Reputation: 46229

You can try to use cte recursive to make a calendar table then do outer join

Declare @Start datetime = '2018-01-01 00:00:00.000',@End datetime ='2018-02-01 00:00:00.000';

;WITH CTE AS (
    SELECT @Start startTime,DATEADD(MINUTE,15,@Start) nextTime,@End endTime
    UNION ALL
    SELECT DATEADD(MINUTE,15,startTime),DATEADD(MINUTE,15,nextTime) nextTime,@End
    FROM CTE
    WHERE DATEADD(MINUTE,15,startTime) < @End
)
SELECT startTime,nextTime,COUNT(t1.ID)
FROM CTE c1 
LEFT JOIN T t1 on t1.Time BETWEEN c1.startTime and c1.nextTime
GROUP BY startTime,nextTime
option ( MaxRecursion 0 );

Note

The CTE default maximum recursion is 100, you can sett option ( MaxRecursion 0 );

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Sqlfiddle

Upvotes: 2

Related Questions