11xMNAyS

1CREATE temporary TABLE IF NOT EXISTS surveys_temp AS 
2  (SELECT SN.id_rep, 
3          Coalesce(( Sum(CASE 
4                           WHEN SN.score < 7 THEN -100 
5                           WHEN SN.score >= 7 
6                                AND score < 9 THEN 0 
7                           WHEN SN.score >= 9 THEN 100 
8                         end) / Count(score) ), 0) AS NRS, 
9          SW.wtr 
10   FROM   surveys SN 
11          INNER JOIN (SELECT id_rep, 
12                             Coalesce(( Sum(CASE 
13                                              WHEN score < 7 THEN -100 
14                                              WHEN score >= 7 
15                                                   AND score < 9 THEN 0 
16                                              WHEN score >= 9 THEN 100 
17                                            end) / Count(score) ), 0) AS WTR 
18                      FROM   surveys 
19                      WHERE  survey_type = 'WTR' 
20                      GROUP  BY id_rep) SW 
21                  ON SW.id_rep = SN.id_rep 
22   WHERE  SN.survey_type = 'NRS' 
23   GROUP  BY SN.id_rep); 
24
25CREATE temporary TABLE IF NOT EXISTS orders_temp AS 
26  (SELECT id_rep, 
27          Sum(Cast(ordernumber AS DECIMAL(2, 0))) AS Orders 
28   FROM   orders 
29   GROUP  BY id_rep); 
30
31CREATE temporary TABLE IF NOT EXISTS chats_temp AS 
32  (SELECT id_rep, 
33          Time_format(Sec_to_time(Cast(Cast(Sum(response_time * -1)/ Count( 
34                                  id_session) AS 
35                                  DECIMAL(5, 2 
36                                              )) AS CHAR(6 
37                                  ))), '%H : %i : %s')AS response_time 
38   FROM   chats 
39   WHERE  chat_type = 1 
40   GROUP  BY id_rep 
41   ORDER  BY id_rep); 
42
43SELECT R.rep_name, 
44       Count(DISTINCT R.id_session)                AS Chats, 
45       O.orders, 
46       Concat(Cast((o.orders/Count(DISTINCT r.id_session)) * 100 AS DECIMAL(5, 2 
47              )), '%' 
48       )                                           AS CONVERSION, 
49       Coalesce(Cast(s.nrs AS DECIMAL(5, 2)), '0') AS NRS, 
50       Coalesce(Cast(s.wtr AS DECIMAL(5, 2)), '0') AS WTR, 
51       C.response_time 
52FROM   reps R 
53       LEFT JOIN surveys_temp AS S 
54              ON S.id_rep = R.id_rep 
55       LEFT JOIN orders_temp AS O 
56              ON O.id_rep = R.id_rep 
57       LEFT JOIN chats_temp AS C 
58              ON c.id_rep = R.id_rep 
59WHERE  R.rep_country IN( 'D.R', 'U.S' ) 
60GROUP  BY R.rep_name 
61ORDER  BY R.rep_name;