Expressing formula within a SELECT query

Question

I have this existing query:

SELECT 
  extension
, Total_Outbound+Total_Missed+Total_Received AS Total_Calls
, Total_Missed
, Total_Talk_Time_minutes    
FROM (
  SELECT 
, extension       
, sum(if(Answered = 1,0,1)) AS Total_Missed
, sum(CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 1 ELSE 0 END) AS Total_Received
, sum(if(LEGTYPE1 = 1,1,0)) AS Total_Outbound
, round(sum(Duration) / 60,2) AS Total_Talk_Time_minutes

  FROM session a
  GROUP BY extension 
 ) x;

It works great but I need to add a metric/formula to it called missed_call_score right under Total_Talk_Time_Minutes.

The formula for the missed call score is this: (missed calls/total talk time) * (average calls per CSR/total calls) * 100 but one thing to note is that the average calls per csr needs to ignore the MAX and MIN, so the lowest and highest number of calls taken.

I'm not sure how I could construct this score within a single select variable or the syntax I would use for this given the fact that it has to throw out the max and min.

Here is an example of my needed output and the formulas it should be using:

extension | Total calls | missed calls | total talk time | missed call score
----------------------------------------------------------------------------
1234            8               4               15.5            5.7
4321            4               0               9.42            0.0
5678            5               2               6.78            6.5
9876            13              6               18.3            7.2


Total call sum = 30
Total call sum without high and low = 13
average calls per CSR = (13/2) = 6.5

extension 1 = (4/15.5) * (6.5/30) * 100 = 5.7
extension 2 = (0/9.42) * (6.5/30) * 100 = 0.0
extension 3 = (2/6.78) * (6.5/30) * 100 = 6.5
extension 4 = (6/18.3) * (6.5/30) * 100 = 7.2

The data above for extension, total calls, missed calls and talk time are taken from my sql fiddle, linked below. I simply added the score column to give example of my expected output.

The fiddle linked below shows my create and inserts so hopefully that gives everything needed to assist me with this.

**sql fiddle **

http://sqlfiddle.com/#!9/aa1f9/1

UPDATE

Full production query with joins

 SELECT    firstn , 
             lastn , 
             extension , 
             Total_Outbound+Total_Missed+Total_Received AS Total_Calls , 
             Total_Missed , 
             Total_Talk_Time_minutes , 
             Total_All_Calls , 
             Max_Calls , 
             Min_Calls , 
             CSR_Count , 
             ((Total_Missed/Total_Talk_Time_minutes) *  
             (((Total_All_Calls-Max_Calls-Min_Calls)/CSR_Count)/Total_All_Calls)) * 100 
             FROM (   SELECT     u.firstn     , 
             u.lastn     , 
             c.extension        , 
             sum(if(Answered = 1,0,1)) AS Total_Missed , 
             sum(CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 1 ELSE 0 END) AS Total_Received , 
             sum(CASE WHEN LEGTYPE1 = 1 THEN 1 ELSE 0 END) AS Total_Outbound ,
             round(sum(Duration) / 60,2) AS Total_Talk_Time_minutes , 

             (SELECT COUNT(1) FROM ambition.session a INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID    
             INNER join ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID       
             INNER join jackson_id.users u ON c.extension = u.extension  
             WHERE b.ts between curdate() - interval 5 day and now()       
             AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)) Total_All_Calls , 

             (SELECT MAX(CNT) FROM (SELECT COUNT(1) CNT, c.extension 
             FROM ambition.SESSION a INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID       
             INNER join ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID      
             INNER join jackson_id.users u ON c.extension = u.extension 
             WHERE b.ts between curdate() - interval 5 day and now()       
             AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312) GROUP BY responsibleuserextensionid) y) Max_Calls , 

             (SELECT MIN(CNT) FROM (SELECT COUNT(1) CNT, c.extension
             FROM ambition.SESSION a 
             INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID       
             INNER join ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID       
             INNER join jackson_id.users u ON c.extension = u.extension   
             WHERE b.ts between curdate() - interval 5 day and now()       
             AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)GROUP BY responsibleuserextensionid) y) Min_Calls , 

             (SELECT COUNT(DISTINCT c.extension)-2 
             FROM ambition.SESSION a INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID       
             INNER join ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID       
             INNER join jackson_id.users u ON c.extension = u.extension  
             WHERE b.ts between curdate() - interval 5 day and now()       
             AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)) CSR_Count       

             FROM ambition.session a       
             INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID       
             INNER join ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID       
             INNER join jackson_id.users u ON c.extension = u.extension       
             LEFT JOIN ambition.knownnumbers k ON a.callingpartyno = k.phone_number       
             WHERE b.ts between curdate() - interval 5 day and now()       
             AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)       
             GROUP BY c.extension, u.firstn, u.lastn    ) x

user_5420008 · Answer

This should work for you:

SELECT 
  extension
, Total_Outbound+Total_Missed+Total_Received AS Total_Calls
, Total_Missed
, Total_Talk_Time_minutes
, Total_All_Calls
, Max_Calls
, Min_Calls
, CSR_Count
, ((Total_Missed/Total_Talk_Time_minutes) * 
      (((Total_All_Calls-Max_Calls-Min_Calls)/CSR_Count)/Total_All_Calls)) * 100
FROM (
  SELECT 
 extension       
, sum(if(Answered = 1,0,1)) AS Total_Missed
, sum(CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 1 ELSE 0 END) AS Total_Received
, sum(CASE WHEN ANSWERED = 1 AND LEGTYPE1 = 1 THEN 1 ELSE 0 END) AS Total_Outbound
, round(sum(Duration) / 60,2) AS Total_Talk_Time_minutes
, (SELECT COUNT(1) FROM session) Total_All_Calls
, (SELECT MAX(CNT) FROM (SELECT COUNT(1) CNT, EXTENSION FROM SESSION GROUP BY EXTENSION) y) Max_Calls
, (SELECT MIN(CNT) FROM (SELECT COUNT(1) CNT, EXTENSION FROM SESSION GROUP BY EXTENSION) y) Min_Calls
, (SELECT COUNT(DISTINCT EXTENSION)-2 FROM SESSION) CSR_Count

  FROM session a
  GROUP BY extension 
 ) x;

Here is the fiddle.

Basically I used sub-counts in your derived table x to get each of the variables needed for missed_call_score. One major thing worth noting is that the logic was off for Total_Outbound, so I tweaked that to a CASE statement instead of an IF(). I selected the count columns in the outer query just so you can see what is going on, you can remove those.

user_1641564 · Answer

I've done something similar in the past and extracted this snippet from my code.

I think/hope that this might help you getting started (I left out most of the columns from your query and you'd have to adjust avg(amount) to match your formula.

select extension, avg(amount) from 
(
select t.*, 
min(amount) over (partition by extension) as min_amt, 
max(amount) over (partition by extension) as max_amt 
from your_table t
) t
where amount > min_amt and amount < max_amt group by extension;

Expressing formula within a SELECT query

2 Answers