Search the Community

Insert into test.ambition_test(Extension, ExtID, Total_Talk_Time_seconds, Total_Talk_Time_minutes,Total_Outbound, Total_Inbound, Missed_Calls, Total_Calls, Date_of_report, Time_of_report) SELECT c.extension as Extension ,RESPONSIBLEUSEREXTENSIONID as ExtID , sum(Duration) as Total_Talk_Time_seconds , round(sum(Duration) / 60,2) as Total_Talk_Time_minutes , sum(if(LEGTYPE1 = 1,1,0)) as Total_Outbound , sum(if(LEGTYPE1 = 2,1,0)) as Total_Inboundambition_test , sum(if(Answered = 1,0,1)) as Missed_Calls , count(DISTINCT b.NOTABLECALLID) as Total_Calls , DATE(NOW()) , NOW() FROM cdrdb.session a LEFT JOIN cdrdb.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID LEFT join cdrdb.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID WHERE b.ts >= curdate() AND c.extension IN (7295,7306,7218,7247,7330,7000,7358) group by c.extension 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), Date_of_report = values(Date_of_report), Time_of_report = values(Time_of_report); The above query is what I have running in MySQL workbench currently. It's pulling and joining several tables to get call data and form metrics for 7 CSR agents on our phone system. IT pulls the data and forms the metrics correctly and it also updates throughout the day properly, but I have a huge problem: It will not insert new records on each new day, it still overwrites per day whereas I want to keep a history. I created a unique index on the Primary key Extension as well as the Date_of_report column as well. To specify, this query runs every 15 minutes to add/aggregate the metric totals based on the extension, however, the next day we should be inserting 7 new records and then have those aggregate through the day. Basically by the end of 3 days, we would have 21 records. However, this just continues to overwrite every day. Any ideas or tips here?