Query examples

From Genesys Documentation
Jump to: navigation, search
This topic is part of the manual Workforce Management ETL Database Reference for version Current of Workforce Management.

Use these examples to construct three different categories of database queries for the WFM ETL database schema.

Related documentation:

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 &gt;= ?

AND WFM_SCH_AGENT_STATE.WFM_STATE_START &lt; ?

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 &gt;= ? AND WFM_SCH_AGENT_DAY.WFM_DATE &lt;= ?

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 &gt;= ? AND WFM_SCH_AGENT_DAY.WFM_DATE &lt;= ?

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 &gt;= ? AND WFM_SCH_AGENT_STATE.WFM_STATE_START &lt; ?

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 &gt;= ? AND WFM_SCH_AGENT_STATE.WFM_STATE_START &lt; ?

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 &gt;= ? AND WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP &lt; ?

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 &gt;= ? AND WFM_SCH_AGENT_DAY.WFM_DATE &lt;= ?

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 &gt;= '11/14/2013' AND WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP &lt; '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 &gt;= '11/14/2013' AND WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP &lt; '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 &gt;= ? AND WFM_PERF_ITEM_DAY.WFM_DATE &lt;= ? 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 &gt;= ? AND WFM_PERF_ITEM_DAY.WFM_DATE &lt;= ? 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 &gt;= ? AND WFM_PERF_ITEM_DAY.WFM_DATE &lt;= ? 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 &gt;= ? AND WFM_PERF_ITEM_DAY.WFM_DATE &lt;= ? 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 &gt;= ? AND WFM_TIME_STEP &lt; ? 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

Retrieved from "https://all.docs.genesys.com/PEC-WFM/Current/ETLRef/QueryEx (2022-01-20 20:04:53)"