Skip to main content
Database Administrators

Return to Answer

replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link
Community Bot
Community Bot
  • 1

This is a classic example of how a "Numbers table""Numbers table" can really help get the results you need.

Looking at your commentyour comment on @Thofle's answer, it looks like you want to see all time intervals, even if there were no calls during the given interval. To accomplish that, you can simply modify the select query to use a LEFT JOIN, and COUNT(...) the number of Calls rows, like:

This is a classic example of how a "Numbers table" can really help get the results you need.

Looking at your comment on @Thofle's answer, it looks like you want to see all time intervals, even if there were no calls during the given interval. To accomplish that, you can simply modify the select query to use a LEFT JOIN, and COUNT(...) the number of Calls rows, like:

This is a classic example of how a "Numbers table" can really help get the results you need.

Looking at your comment on @Thofle's answer, it looks like you want to see all time intervals, even if there were no calls during the given interval. To accomplish that, you can simply modify the select query to use a LEFT JOIN, and COUNT(...) the number of Calls rows, like:

added 681 characters in body
Source Link
Hannah Vernon ♦
Hannah Vernon ♦
  • 70.5k
  • 22
  • 177
  • 321

Looking at your comment on @Thofle's answer, it looks like you want to see all time intervals, even if there were no calls during the given interval. To accomplish that, you can simply modify the select query to use a LEFT JOIN, and COUNT(...) the number of Calls rows, like:

SELECT i.DateStart
    , i.DateEnd
    , TotalCalls = COUNT(c.CallID)
FROM #Intervals i 
    LEFT JOIN #Calls c
        ON i.DateStart <= c.CallStart
            AND i.DateEnd > c.CallStart
GROUP BY i.DateStart
    , i.DateEnd
ORDER BY i.DateStart;

Looking at your comment on @Thofle's answer, it looks like you want to see all time intervals, even if there were no calls during the given interval. To accomplish that, you can simply modify the select query to use a LEFT JOIN, and COUNT(...) the number of Calls rows, like:

SELECT i.DateStart
    , i.DateEnd
    , TotalCalls = COUNT(c.CallID)
FROM #Intervals i 
    LEFT JOIN #Calls c
        ON i.DateStart <= c.CallStart
            AND i.DateEnd > c.CallStart
GROUP BY i.DateStart
    , i.DateEnd
ORDER BY i.DateStart;
Source Link
Hannah Vernon ♦
Hannah Vernon ♦
  • 70.5k
  • 22
  • 177
  • 321

This is a classic example of how a "Numbers table" can really help get the results you need.

Essentially, you create a table containing the 15 minute increments you desire, then join your table to obtain an aggregate number of calls for each 15 minute increment.

In example, I'm using temporary tables for both tables. You'd likely want to make the #Intervals table permanent.

Create the testbed, and populate some sample data:

USE tempdb;

IF (OBJECT_ID('tempdb..#Calls') IS NOT NULL)
DROP TABLE #Calls;

CREATE TABLE #Calls
(
    CallID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CallStart DATETIME NOT NULL
    , CallEnd DATETIME NOT NULL
);

;WITH cte AS
(
    SELECT rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
        , rn1 = ROW_NUMBER() OVER (PARTITION BY o.object_id 
                                     ORDER BY o1.object_id)
    FROM sys.objects o, sys.objects o1
)
INSERT INTO #Calls (CallStart, CallEnd)
SELECT DATEADD(MINUTE, c.rn, DATEADD(DAY, -1, GETDATE()))
    , DATEADD(MINUTE, c.rn + c.rn1, DATEADD(DAY, -1, GETDATE()))
FROM cte c;

IF (OBJECT_ID('tempdb..#Intervals') IS NOT NULL)
DROP TABLE #Intervals;

CREATE TABLE #Intervals
(
    DateStart DATETIME NOT NULL
    , DateEnd DATETIME NOT NULL
);

;WITH cte AS
(
    SELECT TOP(35040) /* approx. number of 15 minute intervals in a year */
        rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id) * 15
    FROM sys.objects o
        , sys.objects o1
        , sys.objects o2
)
INSERT INTO #Intervals (DateStart, DateEnd)
SELECT DATEADD(MINUTE, c.rn, '2015-10-01T00:00:00')
    , DATEADD(MINUTE, c.rn + 15, '2015-10-01T00:00:00')
FROM cte c;

Show the rows in both tables:

SELECT *
FROM #Intervals i
ORDER BY i.DateStart;

SELECT *
FROM #Calls c
ORDER BY c.CallStart;

Join both tables to get the aggregate count of Calls between the 15 minute date ranges:

SELECT i.DateStart
    , i.DateEnd
    , TotalCalls = COUNT(1)
FROM #Calls c
    INNER JOIN #Intervals i ON c.CallStart >= i.DateStart
        AND c.CallStart < i.DateEnd
GROUP BY i.DateStart
    , i.DateEnd
ORDER BY i.DateStart;

On my test platform, I get the following results from the three select statements:

enter image description here