CODEWITHSUNDEEP
sqlt-sql
Jacob Galper
Jacob Galper

Reputation: 31

Count Items in list betwen time range SQL

I have a table that gives me a timestamp of each time an action occurs. Starting with the first action, I want to count how many times an action occurs 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'd like the return to be something like:

16:00: 1

16:30: 8

17:00: 1

How do I get these items to group properly in the respective time period? I have created a temporary table that holds 30 minute intervals between the MAX and MIN values of a column, but I'd like it to start with the first new hour time (:00) and go up from there in 30 minute intervals and count the records as defined 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

Upvotes: 3

Views: 79

Answers (1)

paparazzo
paparazzo

Reputation: 45096

just have a second table
table30

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

this just deals with the minutes 

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

Upvotes: 1

Related Questions