Number of items in list betwen time range SQL

I have a table that gives me a timestamp every time an action happens. Starting with the first activity, I want to count how many times the activity happens every 30 minutes. For example, lets say I have 10 records signed at times: 16:29, 16: 32,16: 35,16: 38,16: 45,16: 46, 16: 47,16: 58,16: 59,17 : 15

I would like the return to be something like:

16:00: 1

16:30: 8

17:00: 1

How do I get these items to group correctly in the appropriate time frame? I created a temporary table that contains 30 minute intervals between the MAX and MIN values โ€‹โ€‹of the column, but I would like it to start at the first new hour (: 00) and go up from there at 30 minute intervals and count the entries as above

DECLARE @counter smalldatetime,
@startDate smalldatetime,
@endDate smalldatetime,
@interval int = 30

SET @startDate =
(SELECT MIN(date)
FROM table)

SET @endDate =
(SELECT MAX(date)
FROM table)

SET @counter = @startDate
DECLARE @returnDates TABLE (
[date] smalldatetime)

WHILE @counter <= @endDate
BEGIN
INSERT INTO @returnDates ([date])
VALUES (@counter)
SET @counter = DATEADD(n, @interval, @counter)

END

      

+3


source to share


1 answer


there is just a second table,
table30

minAct,  min30  
00, 00
01, 00
...
29, 00
30, 30 
31, 30 
...
59, 30 

      



it only concerns minutes

select table30.min30, count(*) 
  from time 
  join table30 
    on table30.minAct = time.min 
 group by table30.min30

      

+1


source







All Articles