Max IRV Ports in a Hour

Version 1
    This document was generated from CDN thread

    Created by: Ted Gundrum on 24-08-2010 05:19:34 PM
    Hello all
     
    I have been asked to create a report is CUIS 7.5 that will display the max IVR ports, or agents logged on, in a half hour period.  We started with the IVR ports and will apply that to the agent report.  We are pretty close, but seem to be sitting a snag.
     
    Our SQL will scraps the current usage and drops into a custom table. Its supposed to compare it to the last scrape to the current scrape and update only if the value is higher.  If the value goes up, compared to the last scrape, it works great.  If the value goese down, it writes 'null' to the custom table and then never works again.
     
    Our end game would be to get this to work and then run it every second as a stored proc.
     
    Take a look at my SQL code and any thoughts or suggests would be great.
     
    Thanks
    Ted
    Denver, CO
     
     
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    insert into Max_Usage (DateTime, PeripheralID, MaxUsage)
    (
    select Interval = p.CurrentHalfHour,
       p.PeripheralID,
       MaxUsage = sum(CallsInProgress)
    from Peripheral_Real_Time p
    where 0 = (select count (*)
      from Max_Usage mu, Peripheral_Real_Time p
      where mu.DateTime = p.CurrentHalfHour
      and mu.PeripheralID = p.PeripheralID)
     and p.PeripheralID in (5000,5002,5004,5006)
    group by p.CurrentHalfHour, p.PeripheralID
    )

    update Max_Usage
     set MaxUsage = 
      (select updater.M
      from (select Interval = CurrentHalfHour,
        P = PeripheralID,
        M = sum(CallsInProgress)
       from Peripheral_Real_Time
       group by DateTime, PeripheralID, CurrentHalfHour)
      as updater
      where updater.P = PeripheralID
       and updater.Interval = DateTime
       and updater.M > MaxUsage)

    /*
    drop table Max_Usage
    delete from Max_Usage where DateTime is null
    select * from Max_Usage
    create table Max_Usage
     (DateTime datetime null, PeripheralID char (10) null, MaxUsage int null)
    */