All geek questions in one place

Formula expression in SELECT query

I have this existing request:

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 fine, but I need to add a metric / formula to it called missed_call_scoreright below Total_Talk_Time_Minutes.

The formula for evaluating missed calls is: (missed calls/total talk time) * (average calls per CSR/total calls) * 100but it should be noted that average calls per csrMAX and MIN should be ignored, therefore the lowest and highest number of received calls.

I'm not sure how I could build this account within the same selection variable or syntax that I would use for this, given the fact that it should throw max and min.

Here is an example of my required output and the formulas that it should use:

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

, , , , sql, . , .

, , , , .

** sql **

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

UPDATE

 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
+4
2

:

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;

fiddle.

x , missed_call_score. , Total_Outbound, CASE IF(). count , , , .

+2

- .

/, ( , avg (amount) .

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;
+1

Source: https://habr.com/ru/post/1686482/

More articles:


All Articles