CODEWITHSUNDEEP
sqlsql-server-2008
Carlos Monteiro
Carlos Monteiro

Reputation: 45

Group by datetime ranges

I'm using SQL Server 2008 and I would like to know a way to return the count of events grouped by a datetime range. The interval between start and end is always one day.

For example, the query should return something like:

2014-07-01 15:00:00 | 2014-07-02 15:00:00 | 23

2014-07-02 15:00:00 | 2014-07-03 15:00:00 | 32

2014-07-03 15:00:00 | 2014-07-04 15:00:00 | 2123

2014-07-04 15:00:00 | 2014-07-05 15:00:00 | 231

It might appear only one of the date columns, in the result set, but the logic it's the same.

Tks,

Upvotes: 1

Views: 4201

Answers (2)

Grondag
Grondag

Reputation: 102

Interesting question!

I'm assuming you have a single date/time in the table and want to provide a starting and ending date/time values as parameters. I can't compile against SQL Server where I am right now, but something like this should work:

SELECT DATEDIFF (day, start_date, event_time) as period, 
       COUNT (event_time) as event_count
WHERE event_time >= @start_time
  AND event_time <= @end_time
GROUP BY DATEDIFF (day, @start_time, event_time)

If you want to display the starting and ending times for each period, create new computed columns in the select statement by adding the DATEDIFF value to @start_time and @end_time.

Upvotes: 3

Joseph B
Joseph B

Reputation: 5679

If you have event_time in a column, then you can determine the interval in which it falls and find the number of events within each interval, as follows:

The logic is that if the hour is >= 15 (starting from 15:00:00), the start_time would be the same day. For event_times before 15:00:00, the start_time would be the previous day. Similarly, if the hour is >= 15 (starting from 15:00:00), the end_time would be the next day. For event_times before 15:00:00, the end_time would be the current day.

select 
CASE 
  when DATEPART(hh, event_time) >= 15 then DATEADD(HOUR, 15, CAST(CAST(event_time AS DATE) AS DATETIME))
  else DATEADD(DAY, -1, DATEADD(HOUR, 15, CAST(CAST(event_time AS DATE) AS DATETIME)))
END START_TIME,
CASE 
  when DATEPART(hh, event_time) >= 15 then DATEADD(DAY, 1, DATEADD(HOUR, 15, CAST(CAST(event_time AS DATE) AS DATETIME)))
  else DATEADD(HOUR, 15, CAST(CAST(event_time AS DATE) AS DATETIME))
END END_TIME,
count(*) no_of_events
FROM events e
group by 
CASE 
  when DATEPART(hh, event_time) >= 15 then DATEADD(HOUR, 15, CAST(CAST(event_time AS DATE) AS DATETIME))
  else DATEADD(DAY, -1, DATEADD(HOUR, 15, CAST(CAST(event_time AS DATE) AS DATETIME)))
END,
CASE 
  when DATEPART(hh, event_time) >= 15 then DATEADD(DAY, 1, DATEADD(HOUR, 15, CAST(CAST(event_time AS DATE) AS DATETIME)))
  else DATEADD(HOUR, 15, CAST(CAST(event_time AS DATE) AS DATETIME))
END;

SQL Fiddle demo

References:

Update only time from my Datetime field in sql on SO

Upvotes: 1

Related Questions