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.
Best Answer
Note that
b.ts
includes both a date and a time. That's being written to bothdate_of_report
andtime_of_report
.If the primary key is something like (
extension
,date_of_report
) anddate_of_report
is a datatype that truncates the time portion of the data off, then here's what's happening:You're grouping your data by CASR and timem, not CSR and date. Given the nature of phone calls, there will only be one entry for a given CSR at a given time. So, your grouping result in one row per row of source data.
However, your primary key values are based on CSR and date, not time. So, each time for a given date is to be inserted into the destination table as a separate row - but all the time rows with the same date have the same primary key. When you go to insert the second row for a given CSR for a time on a given date, it updates the existing row with the values for that CSR's second call of the day, throwing away the values for the first call of the day - and so on.
So, you're only seeing the last call for each date for the CSR (well, the last call processed, not necessarily the last call chronologically).
Either modify the
UPDATE
portion of the code to add new values to the existing ones (in which case you should always truncate the table before running this), or modify theb.ts
in theGROUP BY
to be a date without a time attached, so you're actually grouping by date.