Jump to: navigation, search
(Created target blank page For Version: PSAAS:Julie)
 
(Update with the copy of version: Public)
Line 1: Line 1:
−
<!-- Creation of the target page -->
+
= Query examples=
 +
{{BEC_Head
 +
|context=
 +
This topic provides examples for the three types of queries that are used in the Workforce Management (WFM) ETL Database schema.
 +
|dimension=ETL
 +
}}
 +
__TOC__
 +
 
 +
==Adherence queries==
 +
 
 +
{|
 +
|-
 +
| Agent Adherence Totals Query (Daily Granularity)
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_ADH_AGENT_DAY.WFM_DATE,<br>
 +
WFM_ADH_AGENT_DAY.WFM_SCHEDULE_DURATION,<br>
 +
WFM_ADH_AGENT_DAY.WFM_NON_ADHERENCE_DURATION,<br>
 +
WFM_ADH_AGENT_DAY.WFM_OUT_SCH_NON_ADH_DURATION,<br>
 +
WFM_ADH_AGENT_DAY.WFM_ADHERENCE_PERC<br>
 +
'''FROM''' WFM_ADH_AGENT_DAY<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_ADH_AGENT_DAY.WFM_SITE_KEY)<br>
 +
JOIN WFM_AGENT '''ON''' (WFM_AGENT.WFM_AGENT_KEY = WFM_ADH_AGENT_DAY.WFM_AGENT_KEY)<br>
 +
LEFT JOIN WFM_TEAM '''ON''' (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_ADH_AGENT_DAY.WFM_DATE &gt;= ? AND WFM_ADH_AGENT_DAY.WFM_DATE &lt;= ?<br>
 +
'''ORDER BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_ADH_AGENT_DAY.WFM_DATE,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME</tt>
 +
</toggledisplay>
 +
|-
 +
| Team Adherence Totals Query (Daily Granularity)
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ADH_AGENT_DAY.WFM_DATE,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
'''SUM'''(WFM_ADH_AGENT_DAY.WFM_SCHEDULE_DURATION),<br>
 +
'''SUM'''(WFM_ADH_AGENT_DAY.WFM_NON_ADHERENCE_DURATION),<br>
 +
'''SUM'''(WFM_ADH_AGENT_DAY.WFM_OUT_SCH_NON_ADH_DURATION)<br>
 +
'''FROM''' WFM_ADH_AGENT_DAY<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_ADH_AGENT_DAY.WFM_SITE_KEY)<br>
 +
JOIN WFM_BU '''ON''' (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY)<br>
 +
LEFT JOIN WFM_TEAM '''ON''' (WFM_TEAM.WFM_TEAM_KEY = WFM_ADH_AGENT_DAY.WFM_TEAM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_ADH_AGENT_DAY.WFM_DATE &gt;= ? AND WFM_ADH_AGENT_DAY.WFM_DATE &lt;= ?<br>
 +
'''GROUP BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ADH_AGENT_DAY.WFM_DATE,<br>
 +
WFM_TEAM.WFM_TEAM_NAME<br>
 +
'''HAVING SUM'''(WFM_ADH_AGENT_DAY.WFM_SCHEDULE_DURATION) &gt; 0<br>
 +
'''ORDER BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ADH_AGENT_DAY.WFM_DATE,<br>
 +
WFM_TEAM.WFM_TEAM_NAME</tt>
 +
</toggledisplay>
 +
|}
 +
 
 +
==Schedule Queries==
 +
 
 +
{|
 +
|-
 +
| Schedule States Query
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.EMPLOYEE_ID,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE,<br>
 +
WFM_STATE.WFM_STATE_NAME,<br>
 +
WFM_SSG.WFM_SSG_NAME,<br>
 +
WFM_SCH_AGENT_STATE.WFM_FULL_DAY,<br>
 +
WFM_SCH_AGENT_STATE.WFM_STATE_START,<br>
 +
WFM_SCH_AGENT_STATE.WFM_STATE_END,<br>
 +
WFM_SCH_AGENT_STATE.WFM_STATE_DURATION,<br>
 +
WFM_SCH_AGENT_STATE.WFM_PAID_DURATION<br>
 +
'''FROM''' WFM_SCH_AGENT_STATE<br>
 +
JOIN WFM_SCH_AGENT_DAY '''ON''' (WFM_SCH_AGENT_DAY.WFM_SCH_AGENT_DAY_KEY = WFM_SCH_AGENT_STATE.WFM_SCH_AGENT_DAY_KEY)<br>
 +
JOIN WFM_AGENT '''ON''' (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY)<br>
 +
LEFT JOIN WFM_TEAM '''ON''' (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY)<br>
 +
JOIN WFM_STATE '''ON''' (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE.WFM_STATE_KEY)<br>
 +
JOIN WFM_STATE_TYPE '''ON''' (WFM_STATE_TYPE.WFM_STATE_TYPE_KEY = WFM_STATE.WFM_STATE_TYPE_KEY)<br>
 +
LEFT JOIN WFM_SSG '''ON''' (WFM_SSG.WFM_SSG_KEY = WFM_STATE.WFM_SSG_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_STATE_TYPE.WFM_STATE_TYPE_NAME NOT IN ('Shift', 'Activity Set', 'Marked Time') AND<br>
 +
WFM_SCH_AGENT_STATE.WFM_STATE_END &gt;= ?<br>
 +
AND WFM_SCH_AGENT_STATE.WFM_STATE_START &lt; ?<br>
 +
'''ORDER BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.EMPLOYEE_ID,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE,<br>
 +
WFM_SCH_AGENT_STATE.WFM_STATE_START,<br>
 +
WFM_STATE.WFM_STATE_KEY</tt>
 +
</toggledisplay>
 +
|-
 +
| Agent Schedule State Totals Query
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE,<br>
 +
'''SUM'''(WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_DURATION)<br>
 +
'''FROM''' WFM_SCH_AGENT_STATE_TIMESTEP<br>
 +
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)<br>
 +
JOIN WFM_AGENT '''ON''' (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY)<br>
 +
LEFT JOIN WFM_TEAM '''ON''' (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY)<br>
 +
JOIN WFM_STATE '''ON''' (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_KEY)<br>
 +
JOIN WFM_SSG '''ON''' (WFM_SSG.WFM_SSG_KEY = WFM_STATE.WFM_SSG_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE &gt;= ? AND WFM_SCH_AGENT_DAY.WFM_DATE &lt;= ?<br>
 +
'''GROUP BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE<br>
 +
'''ORDER BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE</tt>
 +
</toggledisplay>
 +
|-
 +
| Team Schedule State Totals Query
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE,<br>
 +
'''SUM'''(WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_DURATION)<br>
 +
'''FROM''' WFM_SCH_AGENT_STATE_TIMESTEP<br>
 +
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)<br>
 +
JOIN WFM_AGENT '''ON''' (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY)<br>
 +
JOIN WFM_BU '''ON''' (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY)<br>
 +
LEFT JOIN WFM_TEAM '''ON''' (WFM_TEAM.WFM_TEAM_KEY = WFM_SCH_AGENT_DAY.WFM_TEAM_KEY)<br>
 +
JOIN WFM_STATE '''ON''' (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_KEY)<br>
 +
JOIN WFM_SSG '''ON''' (WFM_SSG.WFM_SSG_KEY = WFM_STATE.WFM_SSG_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE &gt;= ? AND WFM_SCH_AGENT_DAY.WFM_DATE &lt;= ?<br>
 +
'''GROUP BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE<br>
 +
'''ORDER BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE</tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule Marked Time Report Query
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE,<br>
 +
WFM_STATE.WFM_STATE_NAME,<br>
 +
WFM_SCH_AGENT_STATE.WFM_STATE_START,<br>
 +
WFM_SCH_AGENT_STATE.WFM_STATE_END,<br>
 +
WFM_SCH_AGENT_STATE.WFM_STATE_DURATION,<br>
 +
WFM_SCH_AGENT_STATE.WFM_PAID_DURATION<br>
 +
'''FROM''' WFM_SCH_AGENT_STATE<br>
 +
JOIN WFM_SCH_AGENT_DAY '''ON''' (WFM_SCH_AGENT_DAY.WFM_SCH_AGENT_DAY_KEY = WFM_SCH_AGENT_STATE.WFM_SCH_AGENT_DAY_KEY)<br>
 +
JOIN WFM_AGENT '''ON''' (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY)<br>
 +
LEFT JOIN WFM_TEAM '''ON''' (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY)<br>
 +
JOIN WFM_STATE '''ON''' (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE.WFM_STATE_KEY)<br>
 +
JOIN WFM_STATE_TYPE '''ON''' (WFM_STATE_TYPE.WFM_STATE_TYPE_KEY = WFM_STATE.WFM_STATE_TYPE_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_STATE_TYPE.WFM_STATE_TYPE_NAME IN ('Marked Time') AND<br>
 +
WFM_SCH_AGENT_STATE.WFM_STATE_END &gt;= ? AND WFM_SCH_AGENT_STATE.WFM_STATE_START &lt; ?<br>
 +
'''ORDER BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE,<br>
 +
WFM_SCH_AGENT_STATE.WFM_STATE_START,<br>
 +
WFM_STATE.WFM_STATE_KEY</tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule Marked Time Totals Query (Daily Granularity)
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE,<br>
 +
'''SUM'''(WFM_SCH_AGENT_STATE.WFM_STATE_DURATION),<br>
 +
'''SUM'''(WFM_SCH_AGENT_STATE.WFM_PAID_DURATION)<br>
 +
'''FROM''' WFM_SCH_AGENT_STATE<br>
 +
JOIN WFM_SCH_AGENT_DAY '''ON''' (WFM_SCH_AGENT_DAY.WFM_SCH_AGENT_DAY_KEY = WFM_SCH_AGENT_STATE.WFM_SCH_AGENT_DAY_KEY)<br>
 +
JOIN WFM_AGENT '''ON''' (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY)<br>
 +
LEFT JOIN WFM_TEAM '''ON''' (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY)<br>
 +
JOIN WFM_STATE '''ON''' (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE.WFM_STATE_KEY)<br>
 +
JOIN WFM_STATE_TYPE '''ON''' (WFM_STATE_TYPE.WFM_STATE_TYPE_KEY = WFM_STATE.WFM_STATE_TYPE_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_STATE_TYPE.WFM_STATE_TYPE_NAME IN ('Marked Time') AND<br>
 +
WFM_SCH_AGENT_STATE.WFM_STATE_END &gt;= ? AND WFM_SCH_AGENT_STATE.WFM_STATE_START &lt; ?<br>
 +
'''GROUP BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE <br>
 +
'''ORDER BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE</tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule Marked Time Totals Query (Time Step Granularity)
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_DURATION<br>
 +
'''FROM''' WFM_SCH_AGENT_STATE_TIMESTEP<br>
 +
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)<br>
 +
JOIN WFM_AGENT '''ON''' (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY)<br>
 +
LEFT JOIN WFM_TEAM '''ON''' (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY)<br>
 +
JOIN WFM_STATE '''ON''' (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_KEY)<br>
 +
JOIN WFM_STATE_TYPE '''ON''' (WFM_STATE_TYPE.WFM_STATE_TYPE_KEY = WFM_STATE.WFM_STATE_TYPE_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_STATE_TYPE.WFM_STATE_TYPE_NAME IN ('Marked Time') AND<br>
 +
WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP &gt;= ? AND WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP &lt; ?<br>
 +
'''ORDER BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP</tt>
 +
</toggledisplay>
 +
|-
 +
| Weekly Schedule Report Query
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.EMPLOYEE_ID,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE,<br>
 +
WFM_STATE.WFM_STATE_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_FULL_DAY,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DAY_START,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DAY_END,<br>
 +
'''SUM'''(WFM_SCH_AGENT_DAY.WFM_SCHEDULE_DURATION) '''AS''' SCHEDULE_DURATION,<br>
 +
'''SUM'''(WFM_SCH_AGENT_DAY.WFM_PAID_DURATION) '''AS''' PAID_DURATION,<br>
 +
'''SUM'''(WFM_SCH_AGENT_DAY.WFM_WORK_DURATION) '''AS''' WORK_DURATION,<br>
 +
'''SUM'''(WFM_SCH_AGENT_DAY.WFM_OVERTIME_DURATION) '''AS''' OVERTIME_DURATION<br> 
 +
'''FROM''' WFM_SCH_AGENT_DAY<br>
 +
JOIN WFM_STATE '''ON''' (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_DAY.WFM_STATE_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY)<br>
 +
JOIN WFM_AGENT '''ON''' (WFM_AGENT.WFM_AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY)<br>
 +
LEFT JOIN WFM_TEAM '''ON''' (WFM_TEAM.WFM_TEAM_KEY = WFM_AGENT.WFM_TEAM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE &gt;= ? AND WFM_SCH_AGENT_DAY.WFM_DATE &lt;= ?<br>
 +
'''GROUP BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.EMPLOYEE_ID,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE,<br>
 +
WFM_STATE.WFM_STATE_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DAY_START,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DAY_END,<br>
 +
WFM_SCH_AGENT_DAY.WFM_FULL_DAY<br> 
 +
'''ORDER BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_TEAM.WFM_TEAM_NAME,<br>
 +
WFM_AGENT.EMPLOYEE_ID,<br>
 +
WFM_AGENT.FIRST_NAME,<br>
 +
WFM_AGENT.LAST_NAME,<br>
 +
WFM_SCH_AGENT_DAY.WFM_DATE</tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule State Group (SSG) Totals Query
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_SSG.WFM_SSG_NAME,<br>
 +
'''SUM'''(WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_DURATION) / 15 '''AS''' WFM_SSG_TOTAL,<br>
 +
WFM_SSG.WFM_SSG_WEIGHT<br> 
 +
'''FROM''' WFM_SCH_AGENT_STATE_TIMESTEP<br>
 +
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)<br>
 +
JOIN WFM_AGENT '''ON''' (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY)<br>
 +
JOIN WFM_BU '''ON''' (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY)<br>
 +
JOIN WFM_STATE '''ON''' (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_KEY)<br>
 +
JOIN WFM_SSG '''ON''' (WFM_SSG.WFM_SSG_KEY = WFM_STATE.WFM_SSG_KEY)<br>
 +
'''WHERE'''<br>
 +
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'<br>
 +
AND WFM_SITE.WFM_SITE_NAME = 'Sched Pot 4'<br>
 +
'''GROUP BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_SSG.WFM_SSG_NAME,<br>
 +
WFM_SSG.WFM_SSG_WEIGHT<br> 
 +
'''ORDER BY'''<br>
 +
WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_SSG.WFM_SSG_WEIGHT</tt>
 +
</toggledisplay>
 +
|-
 +
| Activity Schedule Coverage Query
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP,<br>
 +
'''SUM'''(WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_DURATION) / 15 '''AS''' WFM_ACTIVITY_COVERAGE<br>
 +
'''FROM''' WFM_SCH_AGENT_STATE_TIMESTEP<br>
 +
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)<br>
 +
JOIN WFM_AGENT '''ON''' (WFM_AGENT.AGENT_KEY = WFM_SCH_AGENT_DAY.WFM_AGENT_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_SCH_AGENT_DAY.WFM_SITE_KEY)<br>
 +
JOIN WFM_BU '''ON''' (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY)<br>
 +
JOIN WFM_STATE '''ON''' (WFM_STATE.WFM_STATE_KEY = WFM_SCH_AGENT_STATE_TIMESTEP.WFM_STATE_KEY)<br>
 +
JOIN WFM_STATE_TYPE '''ON''' (WFM_STATE_TYPE.WFM_STATE_TYPE_KEY = WFM_STATE.WFM_STATE_TYPE_KEY)<br>
 +
JOIN WFM_ACTIVITY '''ON''' (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_STATE.WFM_STATE_ID AND WFM_STATE_TYPE.WFM_STATE_TYPE_NAME = 'Activity')<br>
 +
'''WHERE'''<br>
 +
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'<br>
 +
AND WFM_SITE.WFM_SITE_NAME = 'Sched Pot 4'<br>
 +
AND WFM_ACTIVITY.WFM_ACTIVITY_NAME = 'Broadband Priority Care'<br>
 +
'''GROUP BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP<br>
 +
ORDER BY WFM_SCH_AGENT_STATE_TIMESTEP.WFM_TIME_STEP, WFM_ACTIVITY.WFM_ACTIVITY_NAME</tt>
 +
</toggledisplay>
 +
|}
 +
 
 +
==Performance Statistics Queries==
 +
 
 +
{|
 +
|-
 +
| Schedule Daily Summary for Activity
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE,<br>
 +
'''SUM'''(WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_VALUE)<br>
 +
'''FROM''' WFM_PERF_ITEM_DAY<br>
 +
JOIN WFM_ACTIVITY '''ON''' (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_ACTIVITY.WFM_SITE_KEY)<br>
 +
JOIN WFM_PERF_ITEM '''ON''' (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE &gt;= ? AND WFM_PERF_ITEM_DAY.WFM_DATE &lt;= ? AND<br>
 +
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')<br>
 +
'''GROUP BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br> 
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE<br>
 +
'''ORDER BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE<br></tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule Daily Summary for Multi-Site Activity (MSA)
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE,<br>
 +
'''SUM'''(WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_VALUE)<br>
 +
'''FROM''' WFM_PERF_ITEM_DAY<br>
 +
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 &lt;&gt; 10)<br>
 +
JOIN WFM_BU '''ON''' (WFM_BU.WFM_BU_KEY = WFM_ACTIVITY.WFM_BU_KEY)<br>
 +
JOIN WFM_PERF_ITEM '''ON''' (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE &gt;= ? AND WFM_PERF_ITEM_DAY.WFM_DATE &lt;= ? AND<br>
 +
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')<br>
 +
'''GROUP BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE<br>
 +
'''ORDER BY'''<br>
 +
WFM_BU.WFM_BU_NAME,
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE</tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule Daily Summary for Activity Group (AG)
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE,<br>
 +
'''SUM'''(WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_VALUE)<br>
 +
'''FROM''' WFM_PERF_ITEM_DAY<br>
 +
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)<br>
 +
JOIN WFM_BU '''ON''' (WFM_BU.WFM_BU_KEY = WFM_ACTIVITY.WFM_BU_KEY)<br>
 +
JOIN WFM_PERF_ITEM '''ON''' (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE &gt;= ? AND WFM_PERF_ITEM_DAY.WFM_DATE &lt;= ? AND<br>
 +
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')<br>
 +
'''GROUP BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br> 
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE<br>
 +
'''ORDER BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE</tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule Daily Summary for Site
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE,<br>
 +
'''SUM'''(WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_VALUE)<br>
 +
'''FROM''' WFM_PERF_ITEM_DAY<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_PERF_ITEM_DAY.WFM_SITE_KEY)<br>
 +
JOIN WFM_BU '''ON''' (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY)<br>
 +
JOIN WFM_PERF_ITEM '''ON''' (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE &gt;= ? AND WFM_PERF_ITEM_DAY.WFM_DATE &lt;= ? AND<br>
 +
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')<br>
 +
'''GROUP BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br> 
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE<br>
 +
'''ORDER BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE</tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule Intraday Summary for Activity
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE,<br>
 +
'''SUM'''(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE)<br>
 +
'''FROM''' WFM_PERF_ITEM_TIMESTEP<br>
 +
JOIN WFM_PERF_ITEM_DAY '''ON''' (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY)<br>
 +
JOIN WFM_ACTIVITY '''ON''' (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_ACTIVITY.WFM_SITE_KEY)<br>
 +
JOIN WFM_PERF_ITEM '''ON''' (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE = ? AND<br>
 +
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_STAFFING', 'FRC_REQ_STAFFING', 'SCH_COVERAGE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT')<br>
 +
'''GROUP BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE<br>
 +
'''ORDER BY'''<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE</tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule Intraday Summary for Activity
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE,<br>
 +
'''SUM'''(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE)<br>
 +
'''FROM''' WFM_PERF_ITEM_TIMESTEP<br>
 +
JOIN WFM_PERF_ITEM_DAY ON (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY)<br>
 +
JOIN WFM_ACTIVITY ON (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY)<br>
 +
JOIN WFM_SITE ON (WFM_SITE.WFM_SITE_KEY = WFM_ACTIVITY.WFM_SITE_KEY)<br>
 +
JOIN WFM_PERF_ITEM ON (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE = ? AND<br>
 +
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_STAFFING', 'FRC_REQ_STAFFING', 'SCH_COVERAGE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT')<br>
 +
'''GROUP BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE<br>
 +
'''ORDER BY'''<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE</tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule Intraday Summary for Multi-Site Activity (MSA)
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE,<br>
 +
'''SUM'''(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE)<br>
 +
'''FROM''' WFM_PERF_ITEM_TIMESTEP<br>
 +
JOIN WFM_PERF_ITEM_DAY '''ON''' (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY)<br>
 +
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 &lt;&gt; 10)<br>
 +
JOIN WFM_BU '''ON''' (WFM_BU.WFM_BU_KEY = WFM_ACTIVITY.WFM_BU_KEY)<br>
 +
JOIN WFM_PERF_ITEM '''ON''' (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE = ? AND<br>
 +
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_STAFFING', 'FRC_REQ_STAFFING', 'SCH_COVERAGE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT')<br>
 +
'''GROUP BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE<br>
 +
'''ORDER BY'''<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE</tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule Intraday Summary for Activity Group (AG)
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE,<br>
 +
'''SUM'''(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE)<br>
 +
'''FROM''' WFM_PERF_ITEM_TIMESTEP<br>
 +
JOIN WFM_PERF_ITEM_DAY '''ON''' (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY)<br>
 +
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)<br>
 +
JOIN WFM_BU '''ON''' (WFM_BU.WFM_BU_KEY = WFM_ACTIVITY.WFM_BU_KEY)<br>
 +
JOIN WFM_PERF_ITEM '''ON''' (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE = ? AND<br>
 +
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_STAFFING', 'FRC_REQ_STAFFING', 'SCH_COVERAGE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT')<br>
 +
'''GROUP BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE<br>
 +
'''ORDER BY'''<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE</tt>
 +
</toggledisplay>
 +
|-
 +
| Schedule Intraday Summary for Site
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE,<br>
 +
'''SUM'''(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE)<br>
 +
'''FROM''' WFM_PERF_ITEM_TIMESTEP<br>
 +
JOIN WFM_PERF_ITEM_DAY '''ON''' (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_PERF_ITEM_DAY.WFM_SITE_KEY)<br>
 +
JOIN WFM_BU '''ON''' (WFM_BU.WFM_BU_KEY = WFM_SITE.WFM_BU_KEY)<br>
 +
JOIN WFM_PERF_ITEM '''ON''' (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_PERF_ITEM_DAY.WFM_DATE = ? AND<br>
 +
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_STAFFING', 'FRC_REQ_STAFFING', 'SCH_COVERAGE', 'SCH_ASA', 'FRC_CALC_ASA', 'SCH_MAX_OCCUPANCY_PCT', 'FRC_CALC_MAX_OCCUPANCY_PCT')<br>
 +
'''GROUP BY'''<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE<br>
 +
'''ORDER BY'''<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_BU.WFM_BU_NAME,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE</tt>
 +
</toggledisplay>
 +
|-
 +
| Contact Center Performance Report for Activity
 +
| <toggledisplay linkstyle font-size:larger showtext="Show query.. [+]" hidetext="Hide query.. [-]">
 +
<tt>'''SELECT'''<br>
 +
WFM_TIME_STEP,<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE,<br>
 +
'''SUM'''(WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_VALUE)<br>
 +
'''FROM''' WFM_PERF_ITEM_TIMESTEP<br>
 +
JOIN WFM_PERF_ITEM_DAY '''ON''' (WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_DAY_KEY = WFM_PERF_ITEM_TIMESTEP.WFM_PERF_ITEM_DAY_KEY)<br>
 +
JOIN WFM_ACTIVITY '''ON''' (WFM_ACTIVITY.WFM_ACTIVITY_KEY = WFM_PERF_ITEM_DAY.WFM_ACTIVITY_KEY)<br>
 +
JOIN WFM_SITE '''ON''' (WFM_SITE.WFM_SITE_KEY = WFM_ACTIVITY.WFM_SITE_KEY)<br>
 +
JOIN WFM_PERF_ITEM '''ON''' (WFM_PERF_ITEM.WFM_PERF_ITEM_KEY = WFM_PERF_ITEM_DAY.WFM_PERF_ITEM_KEY)<br>
 +
'''WHERE'''<br>
 +
WFM_TIME_STEP  &gt;= ? AND WFM_TIME_STEP &lt; ? AND<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE IN ('ACT_IV', 'ACT_ABANDONED_IV_PCT', 'ACT_AHT', 'ACT_ASA', 'ACT_IV', 'ACT_SERVICE_PCT')<br>
 +
'''GROUP BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP<br>
 +
'''ORDER BY'''<br>
 +
WFM_SITE.WFM_SITE_NAME,<br>
 +
WFM_ACTIVITY.WFM_ACTIVITY_NAME,<br>
 +
WFM_PERF_ITEM_TIMESTEP.WFM_TIME_STEP,<br>
 +
WFM_PERF_ITEM.WFM_PERF_ITEM_CODE</tt>
 +
</toggledisplay>
 +
|}
 +
 
 +
[[Category:V:PSAAS:Julie]]

Revision as of 16:59, June 7, 2019

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.. [+]
Team Adherence Totals Query (Daily Granularity) Show query.. [+]

Schedule Queries

Schedule States Query Show query.. [+]
Agent Schedule State Totals Query Show query.. [+]
Team Schedule State Totals Query Show query.. [+]
Schedule Marked Time Report Query Show query.. [+]
Schedule Marked Time Totals Query (Daily Granularity) Show query.. [+]
Schedule Marked Time Totals Query (Time Step Granularity) Show query.. [+]
Weekly Schedule Report Query Show query.. [+]
Schedule State Group (SSG) Totals Query Show query.. [+]
Activity Schedule Coverage Query Show query.. [+]

Performance Statistics Queries

Schedule Daily Summary for Activity Show query.. [+]
Schedule Daily Summary for Multi-Site Activity (MSA) Show query.. [+]
Schedule Daily Summary for Activity Group (AG) Show query.. [+]
Schedule Daily Summary for Site Show query.. [+]
Schedule Intraday Summary for Activity Show query.. [+]
Schedule Intraday Summary for Activity Show query.. [+]
Schedule Intraday Summary for Multi-Site Activity (MSA) Show query.. [+]
Schedule Intraday Summary for Activity Group (AG) Show query.. [+]
Schedule Intraday Summary for Site Show query.. [+]
Contact Center Performance Report for Activity Show query.. [+]
Comments or questions about this documentation? Contact us for support!