I have a table that looks like this

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

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

CodePudding user response：

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

CodePudding user response：

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];

CodePudding user response：

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;

Demo on db<>fiddle here