Genesys CTI User Forum

Genesys CTI User Forum => Genesys CTI Technical Discussion => Topic started by: Noufal on September 30, 2018, 08:22:32 AM

Title: Agent Logged in time in a Queue on Skill
Post by: Noufal on September 30, 2018, 08:22:32 AM

Dears ,

How we can get Agent Logged in time in a Queue or in a Skill on informart DB ??

Please Share your expertise on this

Title: Re: Agent Logged in time in a Queue on Skill
Post by: hsujdik on September 30, 2018, 06:22:33 PM

For Oracle DB, try this:


[code]
select
  ag.agent_first_name || ' ' || ag.agent_last_name as agent_name,
  ag.resource_name as agent_login,
  ag.employee_id as agent_employee_id,
  s.skill_name as skill_name,
  min(to_char(to_date('19700101','yyyymmdd') + (greatest(srsf.start_ts, rsf.start_ts)/86400) + (dt.label_tz/24), 'dd/mm/yyyy hh24:mi:ss')) as login_time,
  max(to_char(to_date('19700101','yyyymmdd') + (least(srsf.end_ts, rsf.end_ts)/86400) + (dt.label_tz/24), 'dd/mm/yyyy hh24:mi:ss')) as logout_time
from sm_res_session_fact srsf
inner join resource_ ag on ag.resource_key = srsf.resource_key
inner join date_time dt on dt.date_time_key = srsf.start_date_time_key
inner join resource_skill_fact rsf on rsf.resource_key = ag.resource_key
inner join skill s on s.skill_key = rsf.skill_key
where
  1=1
  and dt.label_yyyy_mm_dd = '2018-09-30' -- date in format yyyy-mm-yy
  and s.skill_name in ('Skill_1','Skill_2','Skill_3') -- list of desired skills
group by
  ag.agent_first_name || ' ' || ag.agent_last_name,
  ag.resource_name,
  ag.employee_id,
  s.skill_name
;
[/code]

Title: Re: Agent Logged in time in a Queue on Skill
Post by: Noufal on October 01, 2018, 05:17:37 AM

its great reply and i will try to get the same MS SQL DB

Title: Re: Agent Logged in time in a Queue on Skill
Post by: hsujdik on October 01, 2018, 11:04:30 AM

For SQL Server the only differences are that you’ll have to generatw the functions equivalent to “greatest” and “least” and use the function DateAdd