Hi Tim,
In recent releases, Interaction Optimizer's adherence data can be accessed via an API. For your custom report's output to closely match with Interaction Reporter's reports, I suggest taking a look at this approach. To start on this path, inquire about SCR IC-128638, and there are two methods-ADO.NET and Visual Studio / .NET.
To stay on your current course with SQL and stored procedures, here's an inventory of the major elements to get started (as of 4.0, SU5+):
· Formula: Formula for Adherence Calculation: (1 - (Total Exception time in seconds / Total work time in seconds)) * 100
· Data tables and views:
o AgentActivityLog -- Actual activities can be found in PureConnect's table,.
o IO_SchedulingUnit – Top level Scheduling Unit table, contains various configuration
o IO_ForecastScheduleEntry – One entry is created each week for a scheduling unit
o IO_NamedSchedule – This is the top level weekly schedule for a specific scheduling unit. One schedule for the week will have a value of "Published = 1" and that is the schedule used to calculate adherence off of.
o IO_Schedule – Contains one entry for each day of each agent's schedule.
o IO_ScheduleInterval – Contains every specific activity that makes up a user's schedule
o IO_Status – Used to map a specific IC Status to an ActivityTypeID in IO_Activity table
o IO_Activity – Mapping of ActivityTypeID to Activity Name.
o IO_Agent – List of all agents within Optimizer