Jump to content

Search the Community

Showing results for tags 'upsert'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • Welcome to PHP Freaks
    • Announcements
    • Introductions
  • PHP Coding
    • PHP Coding Help
    • Regex Help
    • Third Party Scripts
    • FAQ/Code Snippet Repository
  • SQL / Database
    • MySQL Help
    • PostgreSQL
    • Microsoft SQL - MSSQL
    • Other RDBMS and SQL dialects
  • Client Side
    • HTML Help
    • CSS Help
    • Javascript Help
    • Other
  • Applications and Frameworks
    • Applications
    • Frameworks
    • Other Libraries
  • Web Server Administration
    • PHP Installation and Configuration
    • Linux
    • Apache HTTP Server
    • Microsoft IIS
    • Other Web Server Software
  • Other
    • Application Design
    • Other Programming Languages
    • Editor Help (Dreamweaver, Zend, etc)
    • Website Critique
    • Beta Test Your Stuff!
  • Freelance, Contracts, Employment, etc.
    • Services Offered
    • Job Offerings
  • General Discussion
    • PHPFreaks.com Website Feedback
    • Miscellaneous

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start





Website URL








Donation Link

Found 1 result

  1. 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?
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.