How to get the average time per second from a date column in SQL?

Question

I have a date column called OrderDate that I use to track insert time.

It will look like this:

I need to be able to use this OrderDate column and get the average inserts per second. So for example there are 3 inserts per minute happening.

How can I use the datetime column and a create a SQL query that will determine the average inserts per minute?

I have tried to Rollup but this is not giving me what I need.

SELECT      
    YEAR(orderdate)  AS YEAR,
    MONTH(orderdate) AS MONTH,
    DAY(orderdate)   AS DAY,
    DATEPART(hour, orderdate)   AS HOUR,
    DATEPART(MINUTE, orderdate) AS MINUTE,
    DATEPART(SECOND, orderdate) AS SECOND
FROM 
    Sales.Orders
GROUP BY 
    ROLLUP (YEAR(orderdate), MONTH(orderdate), DAY(orderdate), 
            DATEPART(HOUR, orderdate),
            DATEPART(MINUTE, orderdate),
            DATEPART(SECOND, orderdate))

It looks like 3 inserts per minute (not per second). Is that what you mean? — Michael Bruesch, Nov 10 '21 at 21:15
Yea that is correct @MichaelBruesch I meant per minute. I need those results. — CodeMan03, Nov 10 '21 at 21:58

Golden Lion · Answer 1 · 2021-11-10T21:39:39.047

use lag to get the prior insertdatetime and datepart to get the second interval

declare @temp as table(AccountNumber int, InsertDateTime datetime)

insert into @temp values(59000,'11/1/2021 1:00:01');
insert into @temp values(59000,'11/1/2021 1:00:03');
insert into @temp values(59000,'11/1/2021 1:00:35');
insert into @temp values(59000,'11/1/2021 1:01:03');
insert into @temp values(59000,'11/1/2021 1:01:04');
insert into @temp values(59000,'11/1/2021 1:01:05');
insert into @temp values(59000,'11/1/2021 1:01:06');

with cte
as
(
  select PriorInsertDateTime,
  InsertDateTime,
  IsNull(DateDiff(second,PriorInsertDateTime,InsertDateTime),0) Duration
  from
  (
  select 
  lag(InsertDateTime,1) over(partition by AccountNumber order by InsertDateTime) 
   PriorInsertDateTime,
   InsertDateTime
   from @temp
   )x
)
select avg(Duration) avg_seconds from cte

output

9 seconds

second durations 0,2,32,28,1,1,1,1

How to get the average time per second from a date column in SQL?

1 Answers1