Query examples

This topic provides examples for the three types of queries that are used in the Workforce Management (WFM) ETL Database schema.

Related Topics

Adherence queries

Agent Adherence Totals Query (Daily Granularity)

Show query.. [+] SELECT WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_ADH_AGENT_DAY.WFM_DATE, WFM_ADH_AGENT_DAY.WFM_SCHEDULE_DURATION, WFM_ADH_AGENT_DAY.WFM_NON_ADHERENCE_DURATION, WFM_ADH_AGENT_DAY.WFM_OUT_SCH_NON_ADH_DURATION, WFM_ADH_AGENT_DAY.WFM_ADHERENCE_PERC FROM WFM_ADH_AGENT_DAY JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_ADH_AGENT_DAY.WFM_SITE_KEY) JOIN WFM_AGENT ON (WFM_AGENT.WFM_AGENT_KEY = WFM_ADH_AGENT_DAY.WFM_AGENT_KEY) LEFT JOIN WFM_TEAM ON (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY) WHERE WFM_ADH_AGENT_DAY.WFM_DATE >= ? AND WFM_ADH_AGENT_DAY.WFM_DATE <= ? ORDER BY WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_ADH_AGENT_DAY.WFM_DATE, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME

Team Adherence Totals Query (Daily Granularity)

Show query.. [+] SELECT WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_ADH_AGENT_DAY.WFM_DATE, WFM_TEAM.WFM_TEAM_NAME, SUM(WFM_ADH_AGENT_DAY.WFM_SCHEDULE_DURATION), SUM(WFM_ADH_AGENT_DAY.WFM_NON_ADHERENCE_DURATION), SUM(WFM_ADH_AGENT_DAY.WFM_OUT_SCH_NON_ADH_DURATION) FROM WFM_ADH_AGENT_DAY JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_ADH_AGENT_DAY.WFM_SITE_KEY) JOIN WFM_BU ON (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY) LEFT JOIN WFM_TEAM ON (WFM_TEAM.WFM_TEAM_KEY = WFM_ADH_AGENT_DAY.WFM_TEAM_KEY) WHERE WFM_ADH_AGENT_DAY.WFM_DATE >= ? AND WFM_ADH_AGENT_DAY.WFM_DATE <= ? GROUP BY WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_ADH_AGENT_DAY.WFM_DATE, WFM_TEAM.WFM_TEAM_NAME HAVING SUM(WFM_ADH_AGENT_DAY.WFM_SCHEDULE_DURATION) > 0 ORDER BY WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_ADH_AGENT_DAY.WFM_DATE, WFM_TEAM.WFM_TEAM_NAME

Schedule Queries

Schedule States Query	Show query.. [+] SELECT WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.EMPLOYEE_ID, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE, WFM_STATE.WFM_STATE_NAME, WFM_SSG.WFM_SSG_NAME, WFM_SCH_AGENT_STATE.WFM_FULL_DAY, WFM_SCH_AGENT_STATE.WFM_STATE_START, WFM_SCH_AGENT_STATE.WFM_STATE_END, WFM_SCH_AGENT_STATE.WFM_STATE_DURATION, WFM_SCH_AGENT_STATE.WFM_PAID_DURATION FROM WFM_SCH_AGENT_STATE JOIN WFM_SCH_AGENT_DAY ON (WFM_SCH_AGENT_DAY.WFM_SCH_AGENT_DAY_KEY = WFM_SCH_AGENT_STATE.WFM_SCH_AGENT_DAY_KEY) JOIN WFM_AGENT ON (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY) LEFT JOIN WFM_TEAM ON (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY) JOIN WFM_STATE ON (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE.WFM_STATE_KEY) JOIN WFM_STATE_TYPE ON (WFM_STATE_TYPE.WFM_STATE_TYPE_KEY = WFM_STATE.WFM_STATE_TYPE_KEY) LEFT JOIN WFM_SSG ON (WFM_SSG.WFM_SSG_KEY = WFM_STATE.WFM_SSG_KEY) WHERE WFM_STATE_TYPE.WFM_STATE_TYPE_NAME NOT IN ('Shift', 'Activity Set', 'Marked Time') AND WFM_SCH_AGENT_STATE.WFM_STATE_END >= ? AND WFM_SCH_AGENT_STATE.WFM_STATE_START < ? ORDER BY WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.EMPLOYEE_ID, WFM_SCH_AGENT_DAY.WFM_DATE, WFM_SCH_AGENT_STATE.WFM_STATE_START, WFM_STATE.WFM_STATE_KEY
Agent Schedule State Totals Query	Show query.. [+] SELECT WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE, SUM(WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_DURATION) FROM WFM_SCH_AGENT_STATE_TIMESTEP JOIN WFM_SCH_AGENT_DAY ON (WFM_SCH_AGENT_DAY.WFM_SCH_AGENT_DAY_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_SCH_AGENT_DAY_KEY) JOIN WFM_AGENT ON (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY) LEFT JOIN WFM_TEAM ON (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY) JOIN WFM_STATE ON (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_KEY) JOIN WFM_SSG ON (WFM_SSG.WFM_SSG_KEY = WFM_STATE.WFM_SSG_KEY) WHERE WFM_SCH_AGENT_DAY.WFM_DATE >= ? AND WFM_SCH_AGENT_DAY.WFM_DATE <= ? GROUP BY WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE ORDER BY WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE
Team Schedule State Totals Query	Show query.. [+] SELECT WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_SCH_AGENT_DAY.WFM_DATE, SUM(WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_DURATION) FROM WFM_SCH_AGENT_STATE_TIMESTEP JOIN WFM_SCH_AGENT_DAY ON (WFM_SCH_AGENT_DAY.WFM_SCH_AGENT_DAY_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_SCH_AGENT_DAY_KEY) JOIN WFM_AGENT ON (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY) JOIN WFM_BU ON (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY) LEFT JOIN WFM_TEAM ON (WFM_TEAM.WFM_TEAM_KEY = WFM_SCH_AGENT_DAY.WFM_TEAM_KEY) JOIN WFM_STATE ON (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_KEY) JOIN WFM_SSG ON (WFM_SSG.WFM_SSG_KEY = WFM_STATE.WFM_SSG_KEY) WHERE WFM_SCH_AGENT_DAY.WFM_DATE >= ? AND WFM_SCH_AGENT_DAY.WFM_DATE <= ? GROUP BY WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_SCH_AGENT_DAY.WFM_DATE ORDER BY WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_SCH_AGENT_DAY.WFM_DATE
Schedule Marked Time Report Query	Show query.. [+] SELECT WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE, WFM_STATE.WFM_STATE_NAME, WFM_SCH_AGENT_STATE.WFM_STATE_START, WFM_SCH_AGENT_STATE.WFM_STATE_END, WFM_SCH_AGENT_STATE.WFM_STATE_DURATION, WFM_SCH_AGENT_STATE.WFM_PAID_DURATION FROM WFM_SCH_AGENT_STATE JOIN WFM_SCH_AGENT_DAY ON (WFM_SCH_AGENT_DAY.WFM_SCH_AGENT_DAY_KEY = WFM_SCH_AGENT_STATE.WFM_SCH_AGENT_DAY_KEY) JOIN WFM_AGENT ON (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY) LEFT JOIN WFM_TEAM ON (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY) JOIN WFM_STATE ON (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE.WFM_STATE_KEY) JOIN WFM_STATE_TYPE ON (WFM_STATE_TYPE.WFM_STATE_TYPE_KEY = WFM_STATE.WFM_STATE_TYPE_KEY) WHERE WFM_STATE_TYPE.WFM_STATE_TYPE_NAME IN ('Marked Time') AND WFM_SCH_AGENT_STATE.WFM_STATE_END >= ? AND WFM_SCH_AGENT_STATE.WFM_STATE_START < ? ORDER BY WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE, WFM_SCH_AGENT_STATE.WFM_STATE_START, WFM_STATE.WFM_STATE_KEY
Schedule Marked Time Totals Query (Daily Granularity)	Show query.. [+] SELECT WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE, SUM(WFM_SCH_AGENT_STATE.WFM_STATE_DURATION), SUM(WFM_SCH_AGENT_STATE.WFM_PAID_DURATION) FROM WFM_SCH_AGENT_STATE JOIN WFM_SCH_AGENT_DAY ON (WFM_SCH_AGENT_DAY.WFM_SCH_AGENT_DAY_KEY = WFM_SCH_AGENT_STATE.WFM_SCH_AGENT_DAY_KEY) JOIN WFM_AGENT ON (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY) LEFT JOIN WFM_TEAM ON (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY) JOIN WFM_STATE ON (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE.WFM_STATE_KEY) JOIN WFM_STATE_TYPE ON (WFM_STATE_TYPE.WFM_STATE_TYPE_KEY = WFM_STATE.WFM_STATE_TYPE_KEY) WHERE WFM_STATE_TYPE.WFM_STATE_TYPE_NAME IN ('Marked Time') AND WFM_SCH_AGENT_STATE.WFM_STATE_END >= ? AND WFM_SCH_AGENT_STATE.WFM_STATE_START < ? GROUP BY WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE ORDER BY WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE
Schedule Marked Time Totals Query (Time Step Granularity)	Show query.. [+] SELECT WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP, WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_DURATION FROM WFM_SCH_AGENT_STATE_TIMESTEP JOIN WFM_SCH_AGENT_DAY ON (WFM_SCH_AGENT_DAY.WFM_SCH_AGENT_DAY_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_SCH_AGENT_DAY_KEY) JOIN WFM_AGENT ON (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY) LEFT JOIN WFM_TEAM ON (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY) JOIN WFM_STATE ON (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_KEY) JOIN WFM_STATE_TYPE ON (WFM_STATE_TYPE.WFM_STATE_TYPE_KEY = WFM_STATE.WFM_STATE_TYPE_KEY) WHERE WFM_STATE_TYPE.WFM_STATE_TYPE_NAME IN ('Marked Time') AND WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP >= ? AND WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP < ? ORDER BY WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP
Weekly Schedule Report Query	Show query.. [+] SELECT WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.EMPLOYEE_ID, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE, WFM_STATE.WFM_STATE_NAME, WFM_SCH_AGENT_DAY.WFM_FULL_DAY, WFM_SCH_AGENT_DAY.WFM_DAY_START, WFM_SCH_AGENT_DAY.WFM_DAY_END, SUM(WFM_SCH_AGENT_DAY.WFM_SCHEDULE_DURATION) AS SCHEDULE_DURATION, SUM(WFM_SCH_AGENT_DAY.WFM_PAID_DURATION) AS PAID_DURATION, SUM(WFM_SCH_AGENT_DAY.WFM_WORK_DURATION) AS WORK_DURATION, SUM(WFM_SCH_AGENT_DAY.WFM_OVERTIME_DURATION) AS OVERTIME_DURATION FROM WFM_SCH_AGENT_DAY JOIN WFM_STATE ON (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_DAY.WFM_STATE_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY) JOIN WFM_AGENT ON (WFM_AGENT.WFM_AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY) LEFT JOIN WFM_TEAM ON (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY) WHERE WFM_SCH_AGENT_DAY.WFM_DATE >= ? AND WFM_SCH_AGENT_DAY.WFM_DATE <= ? GROUP BY WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.EMPLOYEE_ID, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE, WFM_STATE.WFM_STATE_NAME, WFM_SCH_AGENT_DAY.WFM_DAY_START, WFM_SCH_AGENT_DAY.WFM_DAY_END, WFM_SCH_AGENT_DAY.WFM_FULL_DAY ORDER BY WFM_SITE.WFM_SITE_NAME, WFM_TEAM.WFM_TEAM_NAME, WFM_AGENT.EMPLOYEE_ID, WFM_AGENT.FIRST_NAME, WFM_AGENT.LAST_NAME, WFM_SCH_AGENT_DAY.WFM_DATE
Schedule State Group (SSG) Totals Query	Show query.. [+] SELECT WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP, WFM_SSG.WFM_SSG_NAME, SUM(WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_DURATION) / 15 AS WFM_SSG_TOTAL, WFM_SSG.WFM_SSG_WEIGHT FROM WFM_SCH_AGENT_STATE_TIMESTEP JOIN WFM_SCH_AGENT_DAY ON (WFM_SCH_AGENT_DAY.WFM_SCH_AGENT_DAY_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_SCH_AGENT_DAY_KEY) JOIN WFM_AGENT ON (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY) JOIN WFM_BU ON (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY) JOIN WFM_STATE ON (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_KEY) JOIN WFM_SSG ON (WFM_SSG.WFM_SSG_KEY = WFM_STATE.WFM_SSG_KEY) WHERE WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP >= '11/14/2013' AND WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP < '11/15/2013' AND WFM_SITE.WFM_SITE_NAME = 'Sched Pot 4' GROUP BY WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP, WFM_SSG.WFM_SSG_NAME, WFM_SSG.WFM_SSG_WEIGHT ORDER BY WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP, WFM_SSG.WFM_SSG_WEIGHT
Activity Schedule Coverage Query	Show query.. [+] SELECT WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP, SUM(WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_DURATION) / 15 AS WFM_ACTIVITY_COVERAGE FROM WFM_SCH_AGENT_STATE_TIMESTEP JOIN WFM_SCH_AGENT_DAY ON (WFM_SCH_AGENT_DAY.WFM_SCH_AGENT_DAY_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_SCH_AGENT_DAY_KEY) JOIN WFM_AGENT ON (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY) JOIN WFM_BU ON (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY) JOIN WFM_STATE ON (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_KEY) JOIN WFM_STATE_TYPE ON (WFM_STATE_TYPE.WFM_STATE_TYPE_KEY = WFM_STATE.WFM_STATE_TYPE_KEY) JOIN WFM_ACTIVITY ON (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_STATE.WFM_STATE_ID AND WFM_STATE_TYPE.WFM_STATE_TYPE_NAME = 'Activity') WHERE WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP >= '11/14/2013' AND WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP < '11/15/2013' AND WFM_SITE.WFM_SITE_NAME = 'Sched Pot 4' AND WFM_ACTIVITY.WFM_ACTIVITY_NAME = 'Broadband Priority Care' GROUP BY WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP ORDER BY WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP, WFM_ACTIVITY.WFM_ACTIVITY_NAME

Performance Statistics Queries

Schedule Daily Summary for Activity	Show query.. [+] SELECT WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE, SUM(WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_VALUE) FROM WFM_PERF_ITEM_DAY JOIN WFM_ACTIVITY ON (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_ACTIVITY.WFM_SITE_KEY) JOIN WFM_PERF_ITEM ON (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY) WHERE WFM_PERF_ITEM_DAY.WFM_DATE >= ? AND WFM_PERF_ITEM_DAY.WFM_DATE <= ? AND WFM_PERF_ITEM.WFM_PERF_ITEM_CODE IN ('SCH_HEADCOUNT', 'SCH_SERVICE_PCT', 'FRC_CALC_SERVICE_PCT', 'SCH_IV', 'FRC_IV', 'SCH_AHT', 'FRC_AHT', 'FRC_CALC_MAN_HOURS', 'FRC_REQ_MAN_HOURS', 'SCH_MAN_HOURS', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT') GROUP BY WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE ORDER BY WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE
Schedule Daily Summary for Multi-Site Activity (MSA)	Show query.. [+] SELECT WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE, SUM(WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_VALUE) FROM WFM_PERF_ITEM_DAY JOIN WFM_ACTIVITY ON (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY AND WFM_ACTIVITY.WFM_SITE_KEY IS NULL AND WFM_ACTIVITY.WFM_ACTIVITY_TYPE_KEY <> 10) JOIN WFM_BU ON (WFM_BU.WFM_BU_KEY = WFM_ACTIVITY.WFM_BU_KEY) JOIN WFM_PERF_ITEM ON (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY) WHERE WFM_PERF_ITEM_DAY.WFM_DATE >= ? AND WFM_PERF_ITEM_DAY.WFM_DATE <= ? AND WFM_PERF_ITEM.WFM_PERF_ITEM_CODE IN ('SCH_HEADCOUNT', 'SCH_SERVICE_PCT', 'FRC_CALC_SERVICE_PCT', 'SCH_IV', 'FRC_IV', 'SCH_AHT', 'FRC_AHT', 'FRC_CALC_FTE', 'FRC_REQ_FTE', 'SCH_FTE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT') GROUP BY WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE ORDER BY WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE
Schedule Daily Summary for Activity Group (AG)	Show query.. [+] SELECT WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE, SUM(WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_VALUE) FROM WFM_PERF_ITEM_DAY JOIN WFM_ACTIVITY ON (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY AND WFM_ACTIVITY.WFM_SITE_KEY IS NULL AND WFM_ACTIVITY.WFM_ACTIVITY_TYPE_KEY = 10) JOIN WFM_BU ON (WFM_BU.WFM_BU_KEY = WFM_ACTIVITY.WFM_BU_KEY) JOIN WFM_PERF_ITEM ON (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY) WHERE WFM_PERF_ITEM_DAY.WFM_DATE >= ? AND WFM_PERF_ITEM_DAY.WFM_DATE <= ? AND WFM_PERF_ITEM.WFM_PERF_ITEM_CODE IN ('SCH_HEADCOUNT', 'SCH_SERVICE_PCT', 'FRC_CALC_SERVICE_PCT', 'SCH_IV', 'FRC_IV', 'SCH_AHT', 'FRC_AHT', 'FRC_CALC_MAN_HOURS', 'FRC_REQ_MAN_HOURS', 'SCH_MAN_HOURS', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT') GROUP BY WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE ORDER BY WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE
Schedule Daily Summary for Site	Show query.. [+] SELECT WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE, SUM(WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_VALUE) FROM WFM_PERF_ITEM_DAY JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_PERF_ITEM_DAY.WFM_SITE_KEY) JOIN WFM_BU ON (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY) JOIN WFM_PERF_ITEM ON (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY) WHERE WFM_PERF_ITEM_DAY.WFM_DATE >= ? AND WFM_PERF_ITEM_DAY.WFM_DATE <= ? AND WFM_PERF_ITEM.WFM_PERF_ITEM_CODE IN ('SCH_HEADCOUNT', 'SCH_SERVICE_PCT', 'FRC_CALC_SERVICE_PCT', 'SCH_IV', 'FRC_IV', 'SCH_AHT', 'FRC_AHT', 'FRC_CALC_FTE', 'FRC_REQ_FTE', 'SCH_FTE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT') GROUP BY WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE ORDER BY WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_PERF_ITEM_DAY.WFM_DATE, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE
Schedule Intraday Summary for Activity	Show query.. [+] SELECT WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE, SUM(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE) FROM WFM_PERF_ITEM_TIMESTEP JOIN WFM_PERF_ITEM_DAY ON (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY) JOIN WFM_ACTIVITY ON (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_ACTIVITY.WFM_SITE_KEY) JOIN WFM_PERF_ITEM ON (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY) WHERE WFM_PERF_ITEM_DAY.WFM_DATE = ? AND WFM_PERF_ITEM.WFM_PERF_ITEM_CODE IN ('SCH_HEADCOUNT', 'SCH_SERVICE_PCT', 'FRC_CALC_SERVICE_PCT', 'SCH_IV', 'FRC_IV', 'SCH_AHT', 'FRC_AHT', 'FRC_CALC_STAFFING', 'FRC_REQ_STAFFING', 'SCH_COVERAGE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT') GROUP BY WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE ORDER BY WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE
Schedule Intraday Summary for Activity	Show query.. [+] SELECT WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE, SUM(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE) FROM WFM_PERF_ITEM_TIMESTEP JOIN WFM_PERF_ITEM_DAY ON (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY) JOIN WFM_ACTIVITY ON (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_ACTIVITY.WFM_SITE_KEY) JOIN WFM_PERF_ITEM ON (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY) WHERE WFM_PERF_ITEM_DAY.WFM_DATE = ? AND WFM_PERF_ITEM.WFM_PERF_ITEM_CODE IN ('SCH_HEADCOUNT', 'SCH_SERVICE_PCT', 'FRC_CALC_SERVICE_PCT', 'SCH_IV', 'FRC_IV', 'SCH_AHT', 'FRC_AHT', 'FRC_CALC_STAFFING', 'FRC_REQ_STAFFING', 'SCH_COVERAGE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT') GROUP BY WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE ORDER BY WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE
Schedule Intraday Summary for Multi-Site Activity (MSA)	Show query.. [+] SELECT WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE, SUM(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE) FROM WFM_PERF_ITEM_TIMESTEP JOIN WFM_PERF_ITEM_DAY ON (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY) JOIN WFM_ACTIVITY ON (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY AND WFM_ACTIVITY.WFM_SITE_KEY IS NULL AND WFM_ACTIVITY.WFM_ACTIVITY_TYPE_KEY <> 10) JOIN WFM_BU ON (WFM_BU.WFM_BU_KEY = WFM_ACTIVITY.WFM_BU_KEY) JOIN WFM_PERF_ITEM ON (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY) WHERE WFM_PERF_ITEM_DAY.WFM_DATE = ? AND WFM_PERF_ITEM.WFM_PERF_ITEM_CODE IN ('SCH_HEADCOUNT', 'SCH_SERVICE_PCT', 'FRC_CALC_SERVICE_PCT', 'SCH_IV', 'FRC_IV', 'SCH_AHT', 'FRC_AHT', 'FRC_CALC_STAFFING', 'FRC_REQ_STAFFING', 'SCH_COVERAGE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT') GROUP BY WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE ORDER BY WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE
Schedule Intraday Summary for Activity Group (AG)	Show query.. [+] SELECT WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE, SUM(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE) FROM WFM_PERF_ITEM_TIMESTEP JOIN WFM_PERF_ITEM_DAY ON (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY) JOIN WFM_ACTIVITY ON (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY AND WFM_ACTIVITY.WFM_SITE_KEY IS NULL AND WFM_ACTIVITY.WFM_ACTIVITY_TYPE_KEY = 10) JOIN WFM_BU ON (WFM_BU.WFM_BU_KEY = WFM_ACTIVITY.WFM_BU_KEY) JOIN WFM_PERF_ITEM ON (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY) WHERE WFM_PERF_ITEM_DAY.WFM_DATE = ? AND WFM_PERF_ITEM.WFM_PERF_ITEM_CODE IN ('SCH_HEADCOUNT', 'SCH_SERVICE_PCT', 'FRC_CALC_SERVICE_PCT', 'SCH_IV', 'FRC_IV', 'SCH_AHT', 'FRC_AHT', 'FRC_CALC_STAFFING', 'FRC_REQ_STAFFING', 'SCH_COVERAGE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT') GROUP BY WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE ORDER BY WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_BU.WFM_BU_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE
Schedule Intraday Summary for Site	Show query.. [+] SELECT WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE, SUM(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE) FROM WFM_PERF_ITEM_TIMESTEP JOIN WFM_PERF_ITEM_DAY ON (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_PERF_ITEM_DAY.WFM_SITE_KEY) JOIN WFM_BU ON (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY) JOIN WFM_PERF_ITEM ON (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY) WHERE WFM_PERF_ITEM_DAY.WFM_DATE = ? AND WFM_PERF_ITEM.WFM_PERF_ITEM_CODE IN ('SCH_HEADCOUNT', 'SCH_SERVICE_PCT', 'FRC_CALC_SERVICE_PCT', 'SCH_IV', 'FRC_IV', 'SCH_AHT', 'FRC_AHT', 'FRC_CALC_STAFFING', 'FRC_REQ_STAFFING', 'SCH_COVERAGE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT') GROUP BY WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE ORDER BY WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_BU.WFM_BU_NAME, WFM_SITE.WFM_SITE_NAME, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE
Contact Center Performance Report for Activity	Show query.. [+] SELECT WFM_TIME_STEP, WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE, SUM(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE) FROM WFM_PERF_ITEM_TIMESTEP JOIN WFM_PERF_ITEM_DAY ON (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY) JOIN WFM_ACTIVITY ON (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY) JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_ACTIVITY.WFM_SITE_KEY) JOIN WFM_PERF_ITEM ON (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY) WHERE WFM_TIME_STEP >= ? AND WFM_TIME_STEP < ? AND WFM_PERF_ITEM.WFM_PERF_ITEM_CODE IN ('ACT_IV', 'ACT_ABANDONED_IV_PCT', 'ACT_AHT', 'ACT_ASA', 'ACT_IV', 'ACT_SERVICE_PCT') GROUP BY WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP ORDER BY WFM_SITE.WFM_SITE_NAME, WFM_ACTIVITY.WFM_ACTIVITY_NAME, WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP, WFM_PERF_ITEM.WFM_PERF_ITEM_CODE