Cumulative daily totals over 30 day interval

Question

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.

Answer 1

Note that b.ts includes both a date and a time. That's being written to both date_of_report and time_of_report.

If the primary key is something like (extension, date_of_report) and date_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 the b.ts in the GROUP BY to be a date without a time attached, so you're actually grouping by date.

Answer 2

It sounds like you want window functions. They are now available in MySQL 8:

• Introducing Window Functions
• SQL Window Functions for MySQL
• Common Table Expressions and Window Functions in MySQL 8
• MySQL 8 Reference Manual: Window Functions