Sql-server – How to find average of transactions every 15 min in Sql server for last 7 days

sql serversql-server-2012

This question might not be new in forums , but struggling to get the expected out put.
Please any body share the similar requirements if we face with solutions.

I would like to find average of total transactions for every 15 min.

  create table logger

  (starttime datetime,
  endtime datetime,
  response bit)

starttime                      endtime          response
2019-10-14 15:08:38.533 2019-10-14 15:08:38.533 0
2019-10-14 15:08:40.533 2019-10-14 15:08:42.563 0
2019-10-14 15:09:40.533 2019-10-14 15:10:43.563 0
2019-10-14 15:09:41.533 2019-10-14 15:10:43.563 1

expected result:

enter image description here

Below is the script i am using to generate number of seconds.
tried with below articles but no luck to achive the goal.

Script

SELECT DATEDIFF(SECOND,STARTTIME,ENDTIME) FROM logger

Best Answer

Base template:

WITH cte AS ( SELECT 0 num, 
                     @start_date rangestart, 
                     DATEADD(minute, @start_date, @duration) rangeend
              UNION ALL
              SELECT num + 1, 
                     DATEADD(minute, @start_date, @duration * (num + 1)), 
                     DATEADD(minute, @start_date, @duration * (num + 2)) 
              FROM cte 
              WHERE DATEADD(minute, @start_date, @duration * (num + 1)) < @enddate 
            )
SELECT ...
FROM datatable, cte
WHERE datatable.datetime >= cte.rangestart
  AND datatable.datetime < cte.rangeend
GROUP BY cte.num