Max IRV Ports in a Hour

Document created by cdnadmin on Jan 24, 2014
Version 1Show Document
  • View in full screen mode
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.
Denver, CO
insert into Max_Usage (DateTime, PeripheralID, MaxUsage)
select Interval = p.CurrentHalfHour,
   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)