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.

Show us what **you have tried** so far! Where are you stuck? We'll be glad to help - but we won't just write the whole code for you (SO is not a "gimme ze codez" style site - **YOU** need to show some effort first! - you should really know by now, with over 1000 rep points) — marc_s, Nov 17 '15 at 07:30
@ChiragThakar, can you explain why are you not satisfied with Felix's answer? — Giorgi Nakeuri, Dec 02 '15 at 15:44
Hello @giorgi nakeuri, I have already placed comment underneath answer! — Chiragkumar Thakar, Dec 02 '15 at 15:49
@ChiragThakar, I would write the exact same select statement. It should work! Check your data... — Giorgi Nakeuri, Dec 02 '15 at 15:54
yup, i checked that, but i don't want to use tally table too.. — Chiragkumar Thakar, Dec 02 '15 at 15:57
@giorgi nakeuri i checked that, works well. Thanks!! but how about tally table? — Chiragkumar Thakar, Dec 02 '15 at 16:01
@ChiragThakar, actually you can do it with recursive cte which is alittle shorter but tally will outperform all other solutions. Why don't you want tally? — Giorgi Nakeuri, Dec 02 '15 at 16:02
@giorgi nakeuri because i don't have much idea about it, but you are suggesting me to use that so i would work on tally table. Appreciate your help!! — Chiragkumar Thakar, Dec 02 '15 at 16:04

score 6 · Accepted Answer · edited May 23 '17 at 12:32

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.

it shows me startDate from '2015-11-16 00:45:00.000' and for first 3-4 records it shows me random sequence — Chiragkumar Thakar, Nov 17 '15 at 08:19
Does it include intervals without record (count of 0)? I can't run the SQL Fiddle and I've tried to modify the query to fit my use case but it does only return intervals that actually have a count higher than zero. How can I have an entry for each interval (with a count of zero)? — MiniW, Jul 18 '16 at 22:37

score 0 · Answer 2 · answered Nov 17 '15 at 07:52

0

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

answered Nov 17 '15 at 07:52

Tony Rogerson

579
2
6

Thanks for it, but it doesn't show me correct result, i think you should check your query once – Chiragkumar Thakar Nov 17 '15 at 07:57
Each slot shows 15 rows which is right because my test data is a row per minute. I see - you want to overlap the windows and double count rows in the slots - that's simply changing < to <= on the last query. – Tony Rogerson Nov 17 '15 at 08:00
what should i need to do to get my daterange count? – Chiragkumar Thakar Nov 17 '15 at 08:10
You don't need to do anything the COUNT(*) does it for you. Basically you have a table with all the 15 minute interval (window) starts, you then join to your source table using the source data cl_StartTime when the cl_StartTime >= slot start and < adding 15 minutes to the slot start which gives you the next 15 minute window. That make sense? – Tony Rogerson Nov 17 '15 at 08:23
yeah, i got that, it shows me count 15 in entire column of count. – Chiragkumar Thakar Nov 17 '15 at 08:37
Correct, because each of my test windows has an equal number of rows in it just because of they way I created the test data. Try fitting your schema into the query. Just realised you'll probably need to change the INNER JOIN to an OUTER JOIN in case you have no rows in the window - a problem you'd get if you built the windows off your source data. – Tony Rogerson Nov 17 '15 at 08:41
It's not clear - do you want a specific "call" to be in multiple windows if for instance the call duration is 30 minutes you could in two windows? – Tony Rogerson Nov 17 '15 at 08:43
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/95323/discussion-between-chirag-thakar-and-tony-rogerson). – Chiragkumar Thakar Nov 17 '15 at 10:36

BeanFrog · Answer 3 · 2015-12-02T16:12:19.420

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

I've changed the join to not use between any more - it might not behave as you wish when times are on the border. I have tested this on sample data now, and it works fine. If you think it's wrong, please give a little more information - like an example of data that doesn't add up as you expect. — BeanFrog, Dec 02 '15 at 16:13

score 0 · Answer 4 · edited Jan 18 '21 at 12:31

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        
;

Get count based on 15 minutes of interval

4 Answers4