Grouping count by interval of 15 minutes

Question

I was trying to write a query where I can group the data by 15 minutes interval.

Example:

I want to count the orders by interval. So Output should be:

How can this be written?

Answer 1

If you can deal with not counting zeros, you can avoid a calendar table

SELECT 
    DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', TransactionDate) / 15)*15, '20000101'),
    count(*)
FROM 
    Transactions
GROUP BY
    DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', TransactionDate) / 15)*15, '20000101')

gives

2017-07-10 11:30:00.000 2
2017-07-10 11:45:00.000 1
2017-07-10 12:00:00.000 5
2017-07-10 12:15:00.000 1

Answer 2

If you will usually use it, I'd suggest to create a calendar table. In this case this is the minimum expression of this calendar table, you can find a lot of samples googling a bit.

CREATE TABLE MyCalendar (QtHour1 datetime, QtHour2 datetime);

DECLARE @StartDate datetime = '20170101 00:00:00';
DECLARE @EndDate datetime = '20180101 00:00:00';

DECLARE @dt datetime;
SET @dt = @StartDate;

WHILE @dt < @EndDate
BEGIN
    INSERT INTO MyCalendar VALUES(@dt, DATEADD(MINUTE, 15, @dt));
    SET @dt = DATEADD(MINUTE, 15, @dt)
END;
GO

35040 rows affected

CREATE TABLE Transactions(TransactionDate datetime, OrderCount int);
INSERT INTO Transactions VALUES
('20170710 11:37:49', 1),
('20170710 11:41:29', 1),
('20170710 11:58:40', 1),
('20170710 12:04:28', 1),
('20170710 12:05:08', 1),
('20170710 12:10:49', 1),
('20170710 12:11:54', 1),
('20170710 12:12:37', 1),
('20170710 12:15:00', 1);
GO

9 rows affected

Then you can GROUP BY (no matter which field of your calendar table), and get the desired result.

SELECT   QtHour1,
         SUM(OrderCount) as Orders
FROM     MyCalendar
JOIN     Transactions
ON       TransactionDate >= QtHour1
AND      TransactionDate < QtHour2
GROUP BY QtHour1
ORDER BY QtHour1;
GO

QtHour1             | Orders
:------------------ | -----:
10/07/2017 11:30:00 |      2
10/07/2017 11:45:00 |      1
10/07/2017 12:00:00 |      5
10/07/2017 12:15:00 |      1

dbfiddle here