I have the below query which is meant to calculate CSR call metrics and totals per day. The way I run it now for the daily report is to group by extension and place now() and curdate() values in the date and time columns. However, I've now been tasked with modifying this to get a one-time report of daily totals per the 15 CSRs in the last 30 days, so take what it's doing now but gather it for the last 30 days.
After running the below query and selecting * from the ambitionLog30Days table, I have 354 rows which is right for the day count given weekends and absences. However, the totals are not right. For the sum columns below, it returns 1 or 0 so it is essentially only showing one call per CSR per day instead of totals.
Here's the query:
Insert into test.ambitionLog30Days(Extension, ExtID, Total_Talk_Time_seconds,
Total_Talk_Time_minutes,Total_Outbound, Total_Inbound,
Missed_Calls, Total_Calls, Time_of_report,Date_of_report )
SELECT
c.extension
,RESPONSIBLEUSEREXTENSIONID
, sum(Duration)
, round(sum(Duration) / 60,2)
, sum(if(LEGTYPE1 = 1,1,0))
, sum(if(LEGTYPE1 = 2,1,0))
, sum(if(Answered = 1,0,1))
, sum(if(LEGTYPE1 = 1,1,0)) + sum(if(LEGTYPE1 = 2,1,0)) as total_calls
, b.ts
, b.ts
FROM cdrdb.session a
INNER JOIN cdrdb.callsummary b
ON a.NOTABLECALLID = b.NOTABLECALLID
INNER join cdrdb.mxuser c
ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID
WHERE b.ts BETWEEN curdate() - interval 30 day and curdate()
AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)
group by c.extension,b.ts
ON duplicate key update Total_Talk_Time_seconds =values(Total_Talk_Time_seconds),
Total_Talk_Time_minutes =values(Total_Talk_Time_minutes),
Total_Outbound = values(Total_Outbound),
Total_Inbound = values(Total_Inbound),
Missed_calls = values(Missed_calls),
Total_Calls = values(Total_Calls),
Time_of_report = values(Time_of_report);
Session table columns:
RESPONSIBLEUSEREXTENSIONID bigint(20) YES
callsummary table columns:
NOTABLECALLID bigint(20) YES UNI
STARTTIME datetime YES MUL
ENDTIME datetime YES MUL
DURATION int(11) YES MUL
ANSWERED smallint(6) YES
ts timestamp NO MUL CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
firstcallid bigint(20) YES
mxuser table columns:
EXTENSIONID bigint(20) NO PRI 0
USERID bigint(20) YES MUL
ISLAST smallint(6) YES
FIRSTNAME varchar(32) YES
LASTNAME varchar(32) YES
USERPROFILENAME varchar(32) YES MUL
EXTENSION varchar(8) NO
ts timestamp NO MUL CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
PRESENCENOTE varchar(64) YES
Some sample data from current query:
ID | Ext | extID | TTTS | TTTM | TI | TO | MC | TC | DOR | TOR
1 7200 35298 5 0.08 1 1 0 1 2017-08-03 2017-08-03 16:19:48
2 7215 35295 116 1.93 1 1 0 1 2017-08-03 2017-08-03 16:41:32
3 7218 35302 11 0.18 0 1 0 0 2017-08-03 2017-08-03 16:43:47
4 7240 35417 79 1.32 1 1 0 1 2017-08-21 2017-08-21 19:13:38
5 7247 35296 40 0.67 0 1 0 0 2017-08-03 2017-08-03 16:55:50
6 7255 34935 65 1.08 1 1 0 1 2017-08-21 2017-08-21 18:57:07
7 7276 35299 146 2.43 1 1 0 1 2017-08-21 2017-08-21 20:37:18
8 7295 35439 92 1.53 1 1 0 1 2017-08-21 2017-08-21 15:44:36
9 7306 35283 108 1.8 1 1 1 1 2017-08-03 2017-08-03 21:34:47
10 7312 35404 54 0.9 1 1 1 1 2017-08-09 2017-08-09 16:56:51
11 7314 35352 35 0.58 1 1 1 1 2017-08-03 2017-08-03 14:47:33
12 7330 35297 38 0.63 1 1 0 1 2017-08-03 2017-08-03 16:45:05
Expected data should be same format but more than one call per CSR per date. It seems like the upsert may be an issue, but group by could be an issue too.
Now, if I group the above query by only c.extension, I get 15 rows (there are 15 CSRs) and the totals are right but they reflect all 30 days cumulatively, where I need one that has totals for each day in the 30 day interval.