0

I have a table where I record a number of values:

  • channel, cnam (both varchar)
  • calldate (date/time)
  • billsec (integer)
  • disposition (varchar - either 'ANSWERED' or 'NO ANSWER' or 'BUSY')

I need to report on the total number of records and billsec (which is a number of seconds) for certain cnams, along with a breakdown of billsec by disposition, and the average duration.

I have a query that lets me select the correct set of rows to be reported on:

SELECT SUBSTRING(channel,1, LOCATE("-",channel, LENGTH(channel)-8)-1) AS 
chan1,cnam,billsec,disposition 
FROM asteriskcdrdb.cdr
WHERE calldate >= '2017-12-08 00:00:00' AND calldate <= '2017-12-08 
23:59:59' AND (duration-billsec) >=0
HAVING chan1  LIKE 'SIP/3%' or chan1 IN ('SIP/2000','SIP/2016','SIP/2046')
ORDER BY chan1 asc

This returns results like the following:

chanl     | cnam     | billsec  | disposition
----------+----------+----------+----------------
SIP/2000  | foo      |    180   |  ANSWERED
SIP/2000  | foo      |     30   |  NO ANSWER
SIP/2000  | foo      |     60   |  ANSWERED
SIP/3010  | foo foo  |     2    |  BUSY
SIP/3010  | foo foo  |     3    |  BUSY
SIP/3010  | foo foo  |    240   |  ANSWERED
SIP/3020  | foo2     |     30   |  ANSWERED
SIP/3020  | foo2     |    180   |  ANSWERED
SIP/3020  | foo2     |     10   |  NO ANSWER
...

I have the start for how the query for my final results would go:

SELECT cnam,
       sum(disposition) as TOTAL,
       sum(disposition where disposition = 'ANSWERED') as COMPLETED,
       sum(disposition where disposition = 'NO ANSWER' OR case when 
       disposition = 'BUSY' then 1 else 0 end) as MISSED,
       SEC_TO_TIME(sum(billsec)) as 'Total DURATION',
       SEC_TO_TIME(avg(billsec)) as 'AVG DURATION'

My final results should look like this:

CNAM    | TOTAL | COMPLETED | MISSED | Total DURATION | AVG DURATION
--------+-------+-----------+--------+----------------+---------------
foo     |    3  |         3 |      1 |           1:30 |         0:32
foo foo |    3  |         3 |      2 |          03:05 |         1:02
foo2    |    3  |         2 |      1 |          03:34 |         1:12

I even know I can use a subquery to combine all this together. What I can't quite figure out is how to do that, given all the stuff that's in my initial query.

2

Without the DDL and sample (raw) data, and not sure how you go from the first result set having all cnam = "foo foo" to the second result set having CNAM = "foo" or "foo2" ...

You could start with something like ...

select cnam,
       count(disposition) as TOTAL,
       sum(case when disposition = 'ANSWERED' then 1 else 0 end) as COMPLETED,
       sum(case when disposition = 'NO ANSWER' then 1 when disposition = 'BUSY' then 1 else 0 end) as MISSED,
       SEC_TO_TIME(sum(billsec)) as "Total DURATION",
       SEC_TO_TIME(avg(billsec)) as "AVG DURATION"

from   (SELECT SUBSTRING(channel,1, LOCATE("-",channel, LENGTH(channel)-8)-1) AS chan1,
               cnam,
               duration,
               disposition 

        FROM   asteriskcdrdb.cdr

        WHERE  calldate >= '2017-12-08 00:00:00' 
        AND    calldate <= '2017-12-08 23:59:59' 
        AND    (duration-billsec) >=0
        HAVING chan1 LIKE 'SIP/3%' 
            or chan1 IN ('SIP/2000','SIP/2016','SIP/2046')
       ) dt

group by cnam
order by cnam asc

... keep in mind that I'm not 100% sure of your logic (eg, should total/avg duration be based only on COMPLETED data?) so I mostly cut-n-pasted what you had originally.


OK, thinking about this a bit more ...

The derived table isn't really doing anything other than filtering out rows from the base table, which means the derived table could be eliminated.

One possible flattening of the query (using RDFozz's modified WHERE clause):

select cnam,
       count(disposition) as TOTAL,
       sum(case when disposition = 'ANSWERED'  then 1 else 0 end) as COMPLETED,
       sum(case when disposition = 'NO ANSWER' then 1 when disposition = 'BUSY' then 1 else 0 end) as MISSED,
       SEC_TO_TIME(sum(billsec)) as "Total DURATION",
       SEC_TO_TIME(avg(billsec)) as "AVG DURATION"

FROM   asteriskcdrdb.cdr

WHERE  calldate >= '2017-12-08 00:00:00' 
AND    calldate <= '2017-12-08 23:59:59' 
AND    (duration-billsec) >=0
AND    (   channel LIKE 'SIP/3%' 
        OR channel LIKE 'SIP/2000-%' 
        OR channel LIKE 'SIP/2016-%' 
        OR channel LIKE 'SIP/2046-%')

group by cnam
order by cnam asc
  • I updated the data up top. with a more clear explanation. I have tried to run this query and there are some errors. so i am working through them atm. – dreedy Dec 13 '17 at 15:47
  • updated the query to fix a few typos I made while cutting-n-pasting – markp Dec 13 '17 at 15:55
  • That sort of worked. The name, Total DURATION, AVG DURATION are producing results. The TOTAL,COMPLETED,MISSED are all coming up as 0's. – dreedy Dec 13 '17 at 16:29
  • ahh, yeah, looking a bit more closely at your logic ... for TOTAL you need to use count(*), and for the other 2 counters need to sum up 1's and 0's; have updated the query (again), see if that does it – markp Dec 13 '17 at 16:48
  • 1
    @dreedy - FYI: the intent of the HAVING clause is to test aggregate values that can't be checked in the WHERE clause (because they can't be calculated until after the WHERE clause has been used to select the basic row set). As a side-effect, you may be able to use it to perform checks on aliased values (the column names you assign in the query itself, like chanl). However, most queries are more efficient if only aggregates are checked in the HAVING clause (so you don't do a bunch of calculations only to throw them out later). ... – RDFozz Dec 13 '17 at 21:58
0

Wrong syntax; you want:

COUNT(*) AS 'Total'
SUM(m.disposition = 'ANSWERED') AS 'Completed'

(Anyway, a spelling error: dispostion.)

Recommend changing

WHERE calldate >= '2017-12-08 00:00:00'
  AND calldate <= '2017-12-08 23:59:59'

to

WHERE calldate >= '2017-12-08'
  AND calldate  < '2017-12-08' + INTERVAL 1 DAY

Rather than having to do the SUBSTRING..., consider splitting the data as you insert it into the table.

I don't know what you are doing with 2 SELECTs, so I can't advise there. You do not have a "subquery" that I can see.

  • Thanks for getting back with me. I have altered the original statement so that i make a little more. I was originally thinking it needed to be lined up as a sub query. but i am just drawing a blank to how to come to a final output. – dreedy Dec 13 '17 at 15:10

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.