How can I get the count of inserts per minute in SQL

Question

I have a table that looks like this

id	name	CreatedDate
1	test1	2014-06-30 09:00:00
1	test2	2014-06-30 09:01:10
1	test3	2014-06-30 09:01:23
1	test4	2014-06-30 09:01:43
1	test5	2014-06-30 09:02:02
1	test6	2014-06-30 09:02:34
1	test7	2014-06-30 09:03:22
1	test8	2014-06-30 09:03:28
1	test9	2014-06-30 09:04:14
1	test10	2014-06-30 09:04:22
1	test11	2014-06-30 09:04:28

I want to get the number of inserts that have happened per minute so the output looks like this

Inserts Per Min	Start Time	End Time
1	09:00:00	09:00:00
3	09:01:10	09:01:43
2	09:02:02	09:00:34
2	09:03:22	09:03:28
3	09:04:14	09:04:28

How can I do that? This is the code that I have that gives me the Inserts per day but I can't get this to work per minute

Select Count(CreatedDate) as InsertsPerDay, Convert(varchar, CreatedDate, 101) as CreatedDate
From MyTable
Where DATEDIFF(day, CreatedDate, GETDATE())) < 30
Group By Convert(varchar, CreatedDate, 101)
Order By InsertsPerDay DESC

No its just easier to read so I can see how many happened per minute and the minute it occured — Terrance Jackson, Dec 17 '21 at 15:21

score 0 · Answer 1 · answered Dec 17 '21 at 15:41

use subqueries and lag

declare @tmp as table(id int,   name varchar(20),   CreatedDate datetime)

insert into @tmp values(
1,'test1','2014-06-30 09:00:00')
,(1,'test2','2014-06-30 09:01:10')
,(1,'test3','2014-06-30 09:01:23')
,(1,'test4','2014-06-30 09:01:43')
,(1,'test5','2014-06-30 09:02:02')
,(1,'test6','2014-06-30 09:02:34')
,(1,'test7','2014-06-30 09:03:22')
,(1,'test8','2014-06-30 09:03:28')
,(1,'test9','2014-06-30 09:04:14')
,(1,'test1','2014-06-30 09:04:22')
,(1,'test11','2014-06-30 09:04:28')

select
IsNull(sum(case when Seconds between 0 and 60 then 1 end),0) Minute_One,
IsNull(sum(case when Seconds between 61 and 60*2 then 1 end),0) Minute_Two,
IsNull(sum(case when Seconds > 60*2 then 1 end),0) Minute_Others
from 
(
select
(DATEPART(HOUR, DiffCreatedDate) * 3600) +
(DATEPART(MINUTE, DiffCreatedDate) * 60) +
(DATEPART(SECOND, DiffCreatedDate)) Seconds 
from
(
select
CreatedDate-PriorCreatedDate DiffCreatedDate
from
(
select 
CreatedDate,
lag(CreatedDate,1) over(order by CreatedDate) PriorCreatedDate
from @tmp
)x
)y
)z
--order by Seconds

LukStorms · Answer 2 · 2021-12-17T16:04:51.730

This might work on 2008. (but can't verify)

Select 
  Count(CreatedDate) As [Inserts Per Min]
, Min(Cast(CreatedDate As Time(0))) As [Start Time]
, Max(Cast(CreatedDate As Time(0))) As [End Time]
From MyTable
--Where CreatedDate > DateAdd(month, -1, GetDate()) 
Group By Convert(SmallDateTime, Convert(Char(16), CreatedDate, 120))
Order By [Inserts Per Min] Desc;

Inserts Per Min	Start Time	End Time
3	09:01:10	09:01:43
3	09:04:14	09:04:28
2	09:02:02	09:02:34
2	09:03:22	09:03:28
1	09:00:00	09:00:00

Demo on db<>fiddle here

Cetin Basoz · Answer 3 · 2021-12-17T15:55:43.347

DECLARE @Mytimes TABLE
(
    id INT IDENTITY NOT NULL PRIMARY KEY,
    name VARCHAR(10),
    CreatedDate DATETIME
);

INSERT INTO @Mytimes
(
    [name],
    CreatedDate
)
VALUES
('test1', '2014-06-30 09:00:00'),
('test2', '2014-06-30 09:01:10'),
('test3', '2014-06-30 09:01:23'),
('test4', '2014-06-30 09:01:43'),
('test5', '2014-06-30 09:02:02'),
('test6', '2014-06-30 09:02:34'),
('test7', '2014-06-30 09:03:22'),
('test8', '2014-06-30 09:03:28'),
('test9', '2014-06-30 09:04:14'),
('test10', '2014-06-30 09:04:22'),
('test11', '2014-06-30 09:04:28');

WITH TALLY
AS (SELECT TOP (1440)
           ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
    FROM sys.all_columns t1
        CROSS JOIN sys.all_columns t2),
     ranges
AS (SELECT CAST(DATEADD(MINUTE, N - 1, '00:00') AS TIME(0)) AS [from],
           CAST(DATEADD(MINUTE, N, '00:00') AS TIME(0)) AS [to]
    FROM TALLY),
     myTimes
AS (SELECT CAST(CreatedDate AS TIME(0)) ct
    FROM @Mytimes)
--SELECT r.[from],
--       r.[to],
SELECT MIN(t.ct) [from],
       MAX(t.ct) [to],
       COUNT(t.ct)
FROM ranges r
    -- If you want all minutes regardless there is inserts
    --LEFT JOIN myTimes t
    INNER JOIN myTimes t
        ON t.ct >= r.[from]
           AND t.ct < r.[to]
GROUP BY r.[from],
         r.[to]
ORDER BY r.[from];

Note: In case of left join, you would need to edit the select to use coalesce for min(),max() times. ie:

...
SELECT MIN(COALESCE(t.ct, r.[from])) [from],
       MAX(COALESCE(t.ct, r.[to])) [to],
       COUNT(t.ct)
FROM ranges r
    LEFT JOIN myTimes t
        ON t.ct >= r.[from]
           AND t.ct < r.[to]
GROUP BY r.[from],
         r.[to]
ORDER BY r.[from];

How can I get the count of inserts per minute in SQL

3 Answers3