TSQL group by 3pm to 3pm intervals

Question

I am trying to get order counts for everyday this year in 3pm to 3pm intervals

Select cast(order_datetime as date) ,count(*)
from order_table
where order_datetime> '01/01/2015 15:00'
group by ?????

I normally group by

cast(order_datetime as date) ---for 12am to 12am

but I want 3pm to 3pm.

Possible duplicate of [How to group time by hour or by 10 minutes](http://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes) — AleFranz, Dec 21 '15 at 23:59
I don't understand how that applies to this question. that question is about grouping in 10min intervals. I tried `group by (datepart(hour,order_datetime)/24)` but that didnt work. — Jase Villam, Dec 22 '15 at 01:08
I answered a question like this a couple days ago. http://stackoverflow.com/questions/34322662/how-do-you-select-all-records-in-a-24-hour-time-period-and-summarize-the-data-in/34322991#34322991 The group by just needs a comparison start time and then you do a datediff(hh...)/24 based on it. EDIT: It also depends on the structure of your table, so you might need to get more specific than datediff(hh... like datediff(mi... - that answer it was structured with just hourly datetimes. — ZLK, Dec 22 '15 at 01:13
Thank you! that worked! `group by datediff(hh,'1900-01-01 15:00',order_datetime)/24` — Jase Villam, Dec 22 '15 at 01:30

score 0 · Answer 1 · answered Dec 22 '15 at 06:49

This might be solution looking for i have taken a sample data and a table variable @t to solve it col1 in the table refers to Your problem

SET DATEFORMAT DMY
DECLARE @t TABLE
(
Id int,
Name varchar(11),
[Col1] DateTime
)

INSERT INTO @t
VALUES(1,'ABC','22/12/2012 03:45:00 PM'),(2,'SD','22/12/2012 03:01:00 PM'),(3,'SDSA','22/12/2012 02:01:00 PM'),(4,'ASDF','22/12/2012 03:30:00 PM'),(5,'ASWER','22/12/2012 02:30:00 PM')
,(11,'NARI','21/12/2012 03:40:00 PM')

SELECT CASE  WHEN CAST(COL1 AS TIME) >'15:00:00' THEN CAST(DATEADD(DAY,1,Col1) AS date) ELSE CAST(Col1 AS DATE) END AS ORDERS,COUNT(*) AS COUNT
FROM @t
GROUP BY  CASE  WHEN CAST(COL1 AS TIME) >'15:00:00' THEN CAST(DATEADD(DAY,1,Col1) AS date) ELSE CAST(Col1 AS DATE) END

TSQL group by 3pm to 3pm intervals

1 Answers1