Sql-server – Get count based on 15 minutes of interval

countdatesql serversql-server-2008-r2

I have a table containing Call records:

tbl_calls

cl_Id
cl_StartDate
cl_endDate

I am passing two parameters @StartDate and @EndDate to my stored procedure.

My requirement is to get the count of Call Records between every 15 minutes of duration.

For example, if:

@StartDate = '2015-11-16 00:00:00.000', 
@EndDate = '2015-11-16 23:59:00.000'

Output should be:

Date                        Count
2015-11-16 00:00:00.000      10(Count of startDate between '2015-11-16 00:00:00.000' AND '2015-11-16 00:15:00.000')
2015-11-16 00:15:00.000       7(Count of startDate between '2015-11-16 00:15:00.000' AND '2015-11-16 00:30:00.000')
2015-11-16 00:30:00.000      50(Count of startDate between '2015-11-16 00:30:00.000' AND '2015-11-16 00:45:00.000')

upto @EndDate

I've tried the following query, however it doesn't work properly. Is there a better way to do this?

DECLARE @StartDate DATETIME = DATEADD(DAY,-1,GETUTCDATE()),
        @EndDate DATETIME = GETUTCDATE()

SELECT New 
FROM
    (SELECT 
         (CASE 
             WHEN cl_StartTime BETWEEN @StartDate AND 
                         DATEADD(MINUTE, 15, @StartDate) 
                THEN 1 
                ELSE 0 
          END) AS New 
     FROM          
         tbl_Calls WITH (NOLOCK)    
     WHERE 
         cl_StartTime BETWEEN @StartDate AND @EndDate) AS Inners 
GROUP BY 
    New

Let me know if you need further details.

Best Answer

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

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;
Related Question