2

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.

6

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;
  • thank you. The execution time of this query is damn too high.. – Chirag Thakar Nov 18 '15 at 5:10
  • Hi @Chirag - are you saying my query is too slow, or your original query is too slow? – Max Vernon Nov 18 '15 at 15:41
  • Hello, your query, i have modified that and it fulfils my requirement now, Your query is really helpful, Upvote from my side. thanks.. i am not sure why you were inserting big amount of data in #calls table? – Chirag Thakar Nov 18 '15 at 16:06
  • 1
    I typically try to insert a large amount of data into test rigs to ensure performance is acceptable with a reasonable data set. Adding an index to the #Calls table, as in CREATE INDEX IX_Calls ON #Calls(CallStart); makes the results come back very quickly. – Max Vernon Nov 18 '15 at 16:16
1

Something like this might work

SELECT 
  count(1)
  ,DATEADD(MINUTE,(DATEPART(MINUTE, cl_StartDate) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, cl_StartDate), 0)))
FROM tbl_Calls
WHERE 
  cl_StartTime BETWEEN @StartDate AND @EndDate
GROUP BY 
  DATEADD(MINUTE,(DATEPART(MINUTE, cl_StartDate) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, cl_StartDate), 0)))


To get the quarter this will give you a value of 0-3. Multiply by 15 to get 0,15,30,45.

DATEPART(MINUTE, date_recorded) / 15 


To round down cl_StartTime to a full hour.

DATEADD(HOUR, DATEDIFF(HOUR, 0, date_recorded), 0)


Then you just add it together like in the query to get the correct timestamp and group by that.

  • thanks a ton for it, Upvoting because it is helpful, but i want to get all time duration wether that contains count 0 Current O/p is like 9:00 To 9:15 10Chats 9:30 To 9:45 10Chats but what i need is 9:00 To 9:15 10Chats 9:15 To 9:30 0Chats 9:30 To 9:45 10Chats – Chirag Thakar Nov 17 '15 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.