Get count based on 15 minutes of interval

Question

I am working with SQL Server 2008 R2, I have a table:

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 count of records between every 15 minutes of duration

Example:

@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 tried to do it but not getting idea, i am not sure below query is nearby to logic or not.

What I tried is :

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.

Thank you.

Answer 1

This approach generates a list of dates starting with start date, and ending with end date, 15 minutes apart (TableA). It counts all the records in tbl_calls that fall within the 15 minutes and gives the count.

declare @startdate datetime; set @StartDate = '2015-11-16 00:00:00.000';
declare @EndDate datetime; set @EndDate = '2015-11-16 23:59:00.000';

WITH TableA (DateSlotStart, DateSlotEnd) AS (
    SELECT  dateadd(n, (number * 15), @StartDate) AS DateSlotStart  
          , dateadd(n, (number + 1) * 15, @StartDate) AS DateSlotEnd
    FROM master.dbo.spt_values 
    WHERE name IS NULL and dateadd(n, (number) * 15, @StartDate) <= @EndDate
)
select DateSlotStart, DateSlotEnd, count(cl_Id) from tableA
left join @tbl_calls calls on calls.cl_StartDate >= DateSlotStart and calls.cl_StartDate <  DateSlotEnd
group by DateSlotStart, DateSlotEnd

Answer 2

Like others I would suggest creating a table that holds your intervals. This will provide you with the records you need to join to and GROUP BY.

In my example I've created an interval table using a recursive CTE. But there are many other ways you can achieve this, including using a real physical table. I've also created some sample records in another CTE, so anyone can run the example.

You could rework this example using an OUTER JOIN, if you want to find intervals with where no tickets where opened.

Example

DECLARE @StartTime    SMALLDATETIME = '2015-01-01 09:00:00.000'; 
DECLARE @EndTime    SMALLDATETIME = '2015-01-01 10:30:00.000';

WITH tbl_Calls AS
    (
        /* This CTE returns some sample records to help 
         * illistrate the princple.
         * Each sample record has a start and end date 
         * between 9:00 and 10:30 on Jan 1st 2015.
         */
        SELECT
            r.*
        FROM
            (
                VALUES
                    (1, '2015-01-01 09:00:00.000', '2015-01-01 09:30:00.000'),
                    (2, '2015-01-01 09:01:00.000', '2015-01-01 10:00:00.000'),
                    (3, '2015-01-01 09:02:00.000', '2015-01-01 10:15:00.000'),
                    (4, '2015-01-01 09:03:00.000', '2015-01-01 09:14:00.000'),
                    (5, '2015-01-01 09:15:00.000', '2015-01-01 09:16:00.000' ),
                    (6, '2015-01-01 09:16:00.000', '2015-01-01 10:30:00.000')
            ) AS r (cl_Id, cl_StartDate, cl_EndDate)
    ),
    Interval AS
    (
        /* This CTE returns 1 record for each 15 min interval
         * between the start and end time.
         * PLEASE NOTE: This CTE uses recursion to generate the 
         * required records.
         */
            SELECT
                @StartTime                            AS IntervalStart,
                DATEADD(MINUTE, 15, @StartTime)        AS IntervalEnd

        UNION ALL

            SELECT
                -- Addding a millisecond prevents overlapping intervals.
                DATEADD(MILLISECOND, 1, IntervalEnd)    AS IntervalStart,        
                DATEADD(MINUTE, 15, IntervalEnd)        AS IntervalEnd
            FROM
                Interval
            WHERE
                IntervalEnd <= @EndTime
    )
-- ******************************************************
-- The CTEs above this section exist only to provide
-- sample records.
-- The technique for banding date times is shown below.
-- ******************************************************
SELECT        
    i.IntervalStart,
    COUNT(c.cl_Id)        AS RecordsOpened
FROM
    tbl_Calls AS c
        INNER JOIN Interval AS i        ON    c.cl_StartDate >=    i.IntervalStart 
                                        AND c.cl_StartDate <    i.IntervalEnd
GROUP BY
    i.IntervalStart        
;

Answer 3

One approach is to create a table containing your desired slots (see #slots below)....

declare @start_date datetime = '2015-11-17'

select dateadd( minute, rn, '2015-11-17' ) as start_date
into #src
from (
    select row_number() over( order by object_id ) - 1 as rn
    from sys.columns
    ) as rn;

create index cl on #src ( start_date );

select dateadd( minute, 15 * rn, @start_date ) as slot_start
into #slots
from (
    select row_number() over( order by object_id ) - 1 as rn
    from sys.columns
    ) as rn;

select s.slot_start, count(*) as rows_in_slot
from #slots s
    inner join #src d on d.start_date >= s.slot_start 
                     and d.start_date < dateadd( minute, 15, s.slot_start )
group by s.slot_start
order by s.slot_start

Answer 4

First, you need to generate all 15-minute interval starting from @StartDate up to the @EndDate. You can do this with the help of a Tally Table. Then do a LEFT JOIN on tbl_calls to count the number of calls:

SQL Fiddle

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

DECLARE @nRows INT

SELECT @nRows = DATEDIFF(MINUTE, @StartDate, DATEADD(DAY, 1, CAST(@EndDate AS DATE))) / 15

;WITH E1(N) AS(
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b),
Tally(N) AS(
    SELECT TOP(@nRows)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM E8
),
Intervals(sd, ed) AS(
    SELECT
        DATEADD(MINUTE, (t.N - 1) * 15, @StartDate),
        DATEADD(MINUTE, N * 15, @StartDate)
    FROM Tally t
)
SELECT 
    i.sd, i.ed, cnt = COUNT(c.cl_Id)
FROM Intervals i
LEFT JOIN tbl_calls c
    ON i.sd <= c.cl_EndDate
    AND i.ed > c.cl_Startdate
GROUP BY i.sd, i.ed

Take note that the interval generated here is from @StartDate up to the start of @EndDate + 1 day, meaning from '2015-11-16 00:00:00.000' up to '2015-11-17 00:00:00.000'.

Also, the JOIN conditions specifies that the call duration must be between the start of interval and end of interval, but the interval end must not overlap with the call's duration. You can see this answer for more explanation.

You can modify the JOIN condition to your liking, but that's basically the gist.