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