Sql Server query for grouping sum of calls per specific duration

AngelX10 1 Reputation point
2021-04-08T08:16:58.697+00:00

Hi all. I would appreciate it of you could help me write a query. I have a table like this one:

85659-image.png

The end result I would like to be like this:

(Sum of calls based on duration)

85637-image.png

The duration column does not have fixed duration but I want it to be created based on the data that I have. For example if one call has 52 sec duration then the duration column should have data until it reaches the 52 seconds mark (51-55):

85703-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,535 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-04-08T08:45:45.287+00:00

    Hi @AngelX10

    Welcome to the microsoft TSQL Q&A forum!

    For this type of question it is always a good idea to post CREATE TABLE statements for your tables and INSERT statements with the sample data, enough to illustrate all angles of the problem.

    The idea of creating a duration column based on the data you have does not seem to be easy to implement.

    Please refer to the following code for the currently achievable part:

    CREATE TABLE #yourtable(ID INT,[Date] date,Phone INT,[Duration(Sec)] INT)  
    INSERT INTO #yourtable VALUES(1,'1/2/2021',15625,9),(2,'1/2/2021',15789,1),(3,'1/2/2021',15456,12)  
                                 ,(4,'1/2/2021',15462,10),(5,'1/2/2021',15236,15)  
      
    ;WITH cte  
    as(SELECT CASE WHEN [Duration(Sec)]<1 THEN '<1'  
                WHEN [Duration(Sec)] BETWEEN 1 AND 5 THEN '1-5'  
    	        WHEN [Duration(Sec)] BETWEEN 6 AND 10 THEN '6-10'  
    	        WHEN [Duration(Sec)] BETWEEN 11 AND 15 THEN '11-15'  
    	        WHEN [Duration(Sec)] BETWEEN 16 AND 20 THEN '16-20' END Duration  
    FROM #yourtable)  
      
    SELECT Duration,COUNT(Duration) FROM cte  
    GROUP BY Duration  
    

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. AngelX10 1 Reputation point
    2021-04-08T08:58:30.717+00:00

    Thank you EchoLiu-msft

    Next time I will post with create table and insert for ease of use. Thank you.

    I thought of using cases but since i want the duration column to be created based on the data that the user selects I cant use them like that.
    Is it possible to create some kind of loop that creates the duration based on the max duration of the data and use that some how? Maybe save that data duration in a temp table and join it somehow with the main table?

    I am not very familiar with sql server and loops. I am more familiar with c# so excuse me if I wrote something silly.


  3. Olaf Helper 40,156 Reputation points
    2021-04-08T09:47:37.46+00:00

    A more flexible way, base on existing date is

     CREATE TABLE #yourtable(ID INT,[Date] date,Phone INT,[Duration(Sec)] INT)
     INSERT INTO #yourtable VALUES(1,'1/2/2021',15625,9),(2,'1/2/2021',15789,1),(3,'1/2/2021',15456,12)
                                  ,(4,'1/2/2021',15462,10),(5,'1/2/2021',15236,15)
                                  ,(6,'1/2/2021',15236, 52), (7,'1/2/2021',15236, 35);
    GO
    
    ;with cte AS
       (SELECT T.[Duration(Sec)] / 5 AS DurationRange
        FROM #yourtable AS T)
    SELECT DurationRange * 5 + 1 AS DurationFrom, (DurationRange + 1) * 5 AS DurationFrom, COUNT(*) AS Cnt
    FROM cte
    GROUP BY cte.DurationRange
    ORDER BY cte.DurationRange
    
    GO
    
    DROP TABLE #yourtable
    
    0 comments No comments

  4. Viorel 110.7K Reputation points
    2021-04-08T21:00:40.587+00:00

    To include all of durations, try one of approaches:

    ;
    with I as
    (
        select row_number() over (order by value) as i
        from string_split(replicate('x', (select (max(Duration) - 1) / 5 from MyTable)), 'x')
    ), 
    C as
    (
        select i, 
            concat(b, '-', e) as Duration,
            (select count(*) from MyTable where Duration between b and e) as Calls
        from I
        cross apply (values (i * 5 - 4, i * 5)) t(b, e)
        union all
        select 0, '<1', (select count(*) from MyTable where Duration < 1)
    )
    select Duration, Calls
    from C
    order by i
    
    0 comments No comments