Query examples
Use these examples to construct three different categories of database queries for the WFM ETL database schema.
Adherence queries
Agent adherence totals (daily granularity)
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 (daily granularity)
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
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
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
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
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 (daily granularity)
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 (timestep granularity)
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
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 totals
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 v (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 (WM_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
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
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
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<br> 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',<br> '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
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',<br> '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
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 intra-day summary for activity
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 intra-day summary for multi-site activity
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<br> 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 intra-day summary for activity group
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_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 intra-day summary for site
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
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
