CODEWITHSUNDEEP
sqlsql-server
Ray
Ray

Reputation: 103

Counting Calls by Half-Hour intervals

I was trying to get the count of calls per half-hour interval. Couldn't figure it out.

select 
       count(call_id) as '#Calls',
       1/2 h(date_time) as 'Call_Interval'
from My_Table

enter image description here

Upvotes: 5

Views: 803

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46231

One method to aggregate by various time intervals is with DATEADD and DATEDIFF:

SELECT 
       COUNT(*) as '#Calls',
       DATEADD(minute, (DATEDIFF(minute, '', date_time) / 30) * 30, '') as Call_Interval
FROM dbo.My_Table
GROUP BY DATEADD(minute, (DATEDIFF(minute, '', date_time) / 30) * 30, '')
ORDER BY Call_Interval;

On a side note, the empty string constant above represents the default value for datetime. The default values for datetime and other temporal types are listed below, expressed in ISO 8601 string format:

Data Type Default Value
date 1900-01-01
datetime 1900-01-01T00:00:00
datetime2 1900-01-01T00:00:00
datetimeoffset 1900-01-01T00:00:00+00:00
smalldatetime 1900-01-01T00:00:00
time 00:00:00

Time interval calculations with a datepart more granular than minute (i.e. second, millisecond, and microsecond) may require a more recent base datetime value than the default value (e.g. 2020-01-01T00:00:00) to avoid overflow.

Upvotes: 7

Related Questions