InfoMart RAA query

[cavagnaro]

Hi guys
Customer BO broke (! Server down) and needs a report from Infomart.
I tried to find the default queries for the BO reports but no luck
Actually never dedicated too much time to understand the tables structure neither 
So customer needs the Agents information for NotReady and ActionCodes on it


By any chance any mercyfull soul has such query? Or at least just NotReady? I see I have to use the view AG2_I_SESS_STATE_DAY
and do a inner join to Date_Time but not having luck with the understanding of the tables.


  if any one has a sample I can grab


Thanks in advance

[cavagnaro]

So reached to this:

[code]
select top 10 dt.label_yyyy_mm_dd, r.AGENT_FIRST_NAME, r.AGENT_LAST_NAME, r.AGENT_NAME,
Sess.Active_time, Sess.READY_TIME, Sess.NOT_READY_TIME, Sess.WRAP_TIME, Sess.BUSY_TIME 
from AG2_I_SESS_STATE_Day Sess
inner join DATE_TIME dt
on Sess.DATE_TIME_KEY = dt.DATE_TIME_KEY
inner join RESOURCE_ r
on r.RESOURCE_KEY = Sess.RESOURCE_KEY
where dt.LABEL_YYYY_MM = '2017-06 '
or dt.LABEL_YYYY_MM = '2017-05'
[/code]





May say that Busy time is all time spent while with a customer, right?
Suggestions?


How could I associate the Group the agent belongs to?

[Tambo]

you could change this to suit possibly

SELECT
DT.LABEL_YYYY_MM_DD_HH24_30INT as Interval,
GRP.GROUP_NAME,   
RG.Resource_Name as "Agent ID",
RG.Agent_First_Name as "Agent First Name",
RG.Agent_Last_Name as "Agent Last Name",

count(case when rsr.SOFTWARE_REASON_VALUE = '200' then SRS.STATE_RSN end) as ADMIN,
count(case when rsr.SOFTWARE_REASON_VALUE = '201' then SRS.STATE_RSN end) as CALL QUALITY FEEDBACK,
count(case when rsr.SOFTWARE_REASON_VALUE = '202' then SRS.STATE_RSN end) as COMFORT BREAK,
count(case when rsr.SOFTWARE_REASON_VALUE = '203' then SRS.STATE_RSN end) as DAILY BRIEF,
count(case when rsr.SOFTWARE_REASON_VALUE = '204' then SRS.STATE_RSN end) as EXTENDED ACW,
count(case when rsr.SOFTWARE_REASON_VALUE = '207' then SRS.STATE_RSN end) as FEEDBACK (1-2-1),
count(case when rsr.SOFTWARE_REASON_VALUE = '205' then SRS.STATE_RSN end) as FREE DIAL,
count(case when rsr.SOFTWARE_REASON_VALUE = '206' then SRS.STATE_RSN end) as LUNCH BREAK,
count(case when rsr.SOFTWARE_REASON_VALUE = '208' then SRS.STATE_RSN end) as SCHEDULED BREAK,
count(case when rsr.SOFTWARE_REASON_VALUE = '213' then SRS.STATE_RSN end) as SIGN OFF,
count(case when rsr.SOFTWARE_REASON_VALUE = '209' then SRS.STATE_RSN end) as TEAM MEETING,
count(case when rsr.SOFTWARE_REASON_VALUE = '210' then SRS.STATE_RSN end) as TRAINING,
count(case when rsr.SOFTWARE_REASON_VALUE = '211' then SRS.STATE_RSN end) as UNWELL CODE,
count(case when rsr.SOFTWARE_REASON_VALUE = '212' then SRS.STATE_RSN end) as VAT,

nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '200' then SRS.STATE_RSN_TIME end), '0') as ADMIN,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '201' then SRS.STATE_RSN_TIME end), '0') as CALL QUALITY FEEDBACK,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '202' then SRS.STATE_RSN_TIME end), '0') as COMFORT BREAK,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '203' then SRS.STATE_RSN_TIME end), '0') as DAILY BRIEF,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '204' then SRS.STATE_RSN_TIME end), '0') as EXTENDED ACW,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '205' then SRS.STATE_RSN_TIME end), '0') as UNPLANNED_DURATION,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '206' then SRS.STATE_RSN_TIME end), '0') as LUNCH BREAK,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '208' then SRS.STATE_RSN_TIME end), '0') as SCHEDULED BREAK,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '213' then SRS.STATE_RSN_TIME end), '0') as SIGN OFF,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '209' then SRS.STATE_RSN_TIME end), '0') as TEAM MEETING,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '210' then SRS.STATE_RSN_TIME end), '0') as TRAINING,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '211' then SRS.STATE_RSN_TIME end), '0') as UNWELL CODE,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE = '212' then SRS.STATE_RSN_TIME end), '0') as VAT,

nvl (sum(case when rsr.SOFTWARE_REASON_VALUE  is not null then SRS.STATE_RSN_TIME end), '0') as Software_Interaction_DURATION,
count(case when rsr.SOFTWARE_REASON_VALUE is  null then SRS.STATE_RSN end) as Hardware_Interactions,
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE  is  null then SRS.STATE_RSN_TIME end), '0') - nvl (sum(case when rsr.hardware_reason in ('PendingACW' , 'ManualSetACWPeriod') then SRS.STATE_RSN_TIME end), '0') as Hardware_Interaction__DURATION,

sum (distinct ses.Not_Ready_Time) - (((
nvl (sum(case when rsr.SOFTWARE_REASON_VALUE  is not null then SRS.STATE_RSN_TIME end), '0') +
nvl (sum(case when rsr.hardware_reason  is NOT null then SRS.STATE_RSN_TIME end), '0')) - (
nvl (sum(case when rsr.hardware_reason in ('PendingACW' , 'ManualSetACWPeriod') then SRS.STATE_RSN_TIME end), '0')))) as Logged_In_Not_Ready,

sum (distinct ses.Not_Ready_Time)as total_not_ready_time

FROM
GIM.AG2_I_SESS_STATE_SUBHR SES
LEFT JOIN GIM.AG2_I_STATE_RSN_SUBHR srs on ((SRS.Date_Time_Key=SES.DATE_TIME_KEY) AND (SRS.Resource_Key=SES.RESOURCE_KEY) AND (SRS.Group_Combination_Key=SES.GROUP_COMBINATION_KEY))
LEFT JOIN GIM.RESOURCE_STATE_REASON rsr on (SRS.RESOURCE_STATE_REASON_KEY = RSR.RESOURCE_STATE_REASON_KEY)
LEFT JOIN GIM.DATE_TIME dt on (SES.DATE_TIME_KEY = DT.DATE_TIME_KEY)
LEFT JOIN GIM.Resource_GI2 rg on (SES.RESOURCE_KEY = RG.RESOURCE_KEY)
LEFT JOIN GIM.RESOURCE_GROUP_COMBINATION rgc on (SES.GROUP_COMBINATION_KEY = RGC.GROUP_COMBINATION_KEY)
LEFT JOIN GIM.GROUP_ grp on (RGC.GROUP_KEY = GRP.GROUP_KEY)

where (dt.CAL_DATE  >= trunc(sysdate, 'DD')-1 AND dt.CAL_DATE  < trunc(sysdate, 'DD'))
and GRP.GROUP_NAME LIKE ('VAG_Manager1')

GROUP BY
DT.LABEL_YYYY_MM_DD_HH24_30INT,
GRP.GROUP_NAME,
RG.Resource_Name,
RG.Agent_First_Name,
RG.Agent_Last_Name

Order by DT.LABEL_YYYY_MM_DD_HH24_30INT ASC

[cavagnaro]

  Thanks! I am trying to understand and modify it

[Tambo]

 

your GIM may be called GIDB.GIM.etc  so you may have to put GIDB. in before all references to GIM
as long as your codes from TServer logs match the codes in CME then it should be good to use.

[cavagnaro]

Oh yeah I got that lol
Trying to understand the sum functions you are doing only

Thanks, made me understand which tables to look at

Enviado de meu E6633 usando Tapatalk