Updating and inserting, Duplicate keys

Question

I've made some changes to this over the last few days and I'm still having issues and unable to produce the correct results.

The following query is for keeping a log of CSR agents by their phone extension and date.

The goal: When this runs throughout the day, it should add any info for extensions not already in the table for the day and update the metrics for the existing ones. If agents 1-5 have been on the phones all day, they're metrics should keep updating and if agent 6 gets on the phones at 3 pm, they should be inserted at that time and update for the rest of the day. However, when the next day starts I want NEW records for all agents to start over, so after 8/8/17, the records for that day should remain and never be overwritten so we can keep a history within this table.

I'm wondering if my problem is with my 'duplicate key update' arguments, but I'm new to that statement so I'm not sure. My primary key is 'Extension', but 'Extension', 'ExtID', and 'Record_date' are all Non NUll. I also put a unique index on 'Extension' and 'Record_date' to make the 'On Duplicate key update' recognize both fields, I"m just not sure it's working.

I just want to update for the current day, and start new records/new inserts on the next day. I.e., if 6 agents work every day for four days, I want the table to have 24 records at the end of the 4th day, I just want the daily records updating throughout.

Thank you for any helpful info. Here's the query:

    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); 

Answer 1

I have made some changes on the query, i have modified the aliases and i think it should be ok now. Try it:

Insert into test.ambition_test(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) as Talk_Time_seconds
    , round(sum(Duration) / 60,2) as Talk_Time_minutes
    , sum(if(LEGTYPE1 = 1,1,0)) as Outbound   
    , sum(if(LEGTYPE1 = 2,1,0)) as Inbound
    , sum(if(Answered = 1,0,1)) as Miss_Calls
    , count(DISTINCT b.NOTABLECALLID) as Calls
    , NOW()
    , curdate() 
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(Talk_Time_seconds), 
    Total_Talk_Time_minutes =values(Talk_Time_minutes), 
    Total_Outbound = values(Outbound), 
    Total_Inbound = values(Inbound), 
    Missed_calls = values(Miss_Calls), 
    Total_Calls = values(Calls), 
    Time_of_report = values(NOW()); 

Answer 2

First of all, you need a column with the day, so you can have a record for each day in the table test.ambition_test of type DATE (no DATETIME). I supose you have created it and it´s called Record_Date.

Then you need to make a unique index on the table test.ambition_test on the columns (Extension, Record_Date) (It will be ok if you make it primary key. You need to make sure that you don´t have another unique key on that table. You can have more indexes, but not unique (neither primary keys).

Then you can use the insert clause. See that i have changed the now() instruction by DATE(NOW()) so you only get the day. In the query, you use Time_of_report, that i think it should be Record_Date (it must be the same that you have in the index)

Insert into test.ambition_test(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 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
    , NOW()
    , DATE(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), 
    Time_of_report = values(Time_of_report);