question

AngelX10-6049 avatar image
0 Votes"
AngelX10-6049 asked Viorel-1 answered

Sql Server query for grouping sum of calls per specific duration

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



sql-server-transact-sql
image.png (6.2 KiB)
image.png (2.3 KiB)
image.png (3.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @AngelX10-6049

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AngelX10-6049 avatar image
0 Votes"
AngelX10-6049 answered AngelX10-6049 commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Do you want to exclude the rows that have no calls (where Calls column is zero)?


0 Votes 0 ·

No I would prefer to include them.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.