CREATE VIEW vwinteractionlegdata AS (
WITH seg AS (
SELECT di.conversationid,
di.conversationstartdateltc,
di.conversationenddateltc,
di.originaldirection,
di.mediatype,
di.purpose,
di.segmenttype,
di.queueid,
di.queuename,
di.userid,
di.agentname,
di.segmentstartdateltc,
di.segmenttime,
di.ani,
di.dnis,
di.wrapupcode,
di.wrapupdesc,
di.disconnectiontype,
di.ttalkcomplete,
di.theldcomplete,
di.tacw,
di.tagentresponsetime,
di.nconsulttransferred,
di.nblindtransferred,
di.tanswered,
di.tuserresponsetime,
di.nconsult,
di.ntransferred,
di.tactivecallback,
di.tactivecallbackcomplete,
di.divisionid,
di.remotedisplayable,
di.externaltag,
di.recordingexists,
(split_part((di.gencode)::text, '|'::text, 1))::integer AS gen1
FROM vwdetailedinteractiondata di
), queue_wait AS (
SELECT seg.conversationid,
seg.queueid,
sum(seg.segmenttime) AS queue_time
FROM seg
WHERE (((seg.purpose)::text = 'acd'::text) AND ((seg.segmenttype)::text = 'interact'::text))
GROUP BY seg.conversationid, seg.queueid
), ivr_time AS (
SELECT seg.conversationid,
sum(seg.segmenttime) AS ivr_time
FROM seg
WHERE (((seg.purpose)::text = 'ivr'::text) AND ((seg.segmenttype)::text = 'ivr'::text))
GROUP BY seg.conversationid
), agent_seg AS (
SELECT seg.conversationid,
seg.conversationstartdateltc,
seg.conversationenddateltc,
seg.originaldirection,
seg.mediatype,
seg.purpose,
seg.segmenttype,
seg.queueid,
seg.queuename,
seg.userid,
seg.agentname,
seg.segmentstartdateltc,
seg.segmenttime,
seg.ani,
seg.dnis,
seg.wrapupcode,
seg.wrapupdesc,
seg.disconnectiontype,
seg.ttalkcomplete,
seg.theldcomplete,
seg.tacw,
seg.tagentresponsetime,
seg.nconsulttransferred,
seg.nblindtransferred,
seg.tanswered,
seg.tuserresponsetime,
seg.nconsult,
seg.ntransferred,
seg.tactivecallback,
seg.tactivecallbackcomplete,
seg.divisionid,
seg.remotedisplayable,
seg.externaltag,
seg.recordingexists,
seg.gen1,
max(
CASE
WHEN ((seg.mediatype)::text = 'callback'::text) THEN 1
ELSE 0
END) OVER (PARTITION BY seg.conversationid, seg.gen1) AS leg_has_callback
FROM seg
WHERE ((seg.purpose)::text = 'agent'::text)
), agent_legs AS (
SELECT agent_seg.conversationid,
agent_seg.gen1,
max((agent_seg.queueid)::text) AS queueid,
max((agent_seg.queuename)::text) AS queuename,
max((agent_seg.userid)::text) AS userid,
max((agent_seg.agentname)::text) AS agentname,
max(agent_seg.conversationstartdateltc) AS conversationstartdateltc,
max(agent_seg.conversationenddateltc) AS conversationenddateltc,
max((agent_seg.originaldirection)::text) AS direction,
max((agent_seg.mediatype)::text) AS mediatype,
min(
CASE
WHEN ((agent_seg.segmenttype)::text = 'interact'::text) THEN agent_seg.segmentstartdateltc
ELSE NULL::timestamp without time zone
END) AS interactionstartdateltc,
sum(
CASE
WHEN ((agent_seg.segmenttype)::text = 'interact'::text) THEN agent_seg.segmenttime
ELSE (0)::numeric
END) AS talk_time,
sum(
CASE
WHEN ((agent_seg.segmenttype)::text = 'hold'::text) THEN agent_seg.segmenttime
ELSE (0)::numeric
END) AS hold_time,
sum(
CASE
WHEN ((agent_seg.segmenttype)::text = 'wrapup'::text) THEN agent_seg.segmenttime
ELSE (0)::numeric
END) AS wrap_up_time,
sum(
CASE
WHEN ((agent_seg.segmenttype)::text = 'alert'::text) THEN agent_seg.segmenttime
ELSE (0)::numeric
END) AS alert_time,
max((agent_seg.ani)::text) AS ani,
max((agent_seg.dnis)::text) AS dnis,
max((
CASE
WHEN ((agent_seg.segmenttype)::text = 'wrapup'::text) THEN agent_seg.wrapupcode
ELSE NULL::character varying
END)::text) AS wrapupcode,
max((
CASE
WHEN ((agent_seg.segmenttype)::text = 'wrapup'::text) THEN agent_seg.wrapupdesc
ELSE NULL::character varying
END)::text) AS wrapupname,
max((
CASE
WHEN ((agent_seg.segmenttype)::text = 'interact'::text) THEN agent_seg.disconnectiontype
ELSE NULL::character varying
END)::text) AS disconnectiontype,
sum(COALESCE(agent_seg.ttalkcomplete, (0)::numeric)) AS ttalkcomplete,
sum(COALESCE(agent_seg.theldcomplete, (0)::numeric)) AS theldcomplete,
sum(COALESCE(agent_seg.tacw, (0)::numeric)) AS tacw,
sum(COALESCE(agent_seg.tagentresponsetime, (0)::numeric)) AS tagentresponsetime,
sum(COALESCE(agent_seg.nconsulttransferred, 0)) AS nconsulttransferred,
sum(COALESCE(agent_seg.nblindtransferred, 0)) AS nblindtransferred,
sum(COALESCE(agent_seg.tanswered, (0)::numeric)) AS tanswered,
sum(COALESCE(agent_seg.tuserresponsetime, (0)::numeric)) AS tuserresponsetime,
sum(COALESCE(agent_seg.nconsult, 0)) AS nconsult,
sum(COALESCE(agent_seg.ntransferred, 0)) AS ntransferred,
sum(COALESCE(agent_seg.tactivecallback, (0)::numeric)) AS tactivecallback,
sum(COALESCE(agent_seg.tactivecallbackcomplete, (0)::numeric)) AS tactivecallbackcomplete,
max((agent_seg.divisionid)::text) AS divisionid,
max((agent_seg.remotedisplayable)::text) AS remotedisplayable,
max((agent_seg.externaltag)::text) AS externaltag,
max(
CASE
WHEN (agent_seg.recordingexists = '1'::"bit") THEN 1
ELSE 0
END) AS recordingexists
FROM agent_seg
WHERE ((agent_seg.leg_has_callback = 0) OR ((agent_seg.mediatype)::text = 'callback'::text))
GROUP BY agent_seg.conversationid, agent_seg.gen1
HAVING (sum(
CASE
WHEN ((agent_seg.segmenttype)::text = 'interact'::text) THEN 1
ELSE 0
END) > 0)
), legs_numbered AS (
SELECT al.conversationid,
al.gen1,
al.queueid,
al.queuename,
al.userid,
al.agentname,
al.conversationstartdateltc,
al.conversationenddateltc,
al.direction,
al.mediatype,
al.interactionstartdateltc,
al.talk_time,
al.hold_time,
al.wrap_up_time,
al.alert_time,
al.ani,
al.dnis,
al.wrapupcode,
al.wrapupname,
al.disconnectiontype,
al.ttalkcomplete,
al.theldcomplete,
al.tacw,
al.tagentresponsetime,
al.nconsulttransferred,
al.nblindtransferred,
al.tanswered,
al.tuserresponsetime,
al.nconsult,
al.ntransferred,
al.tactivecallback,
al.tactivecallbackcomplete,
al.divisionid,
al.remotedisplayable,
al.externaltag,
al.recordingexists,
row_number() OVER (PARTITION BY al.conversationid ORDER BY al.interactionstartdateltc, al.gen1) AS conversationleg,
lead(al.queuename) OVER (PARTITION BY al.conversationid ORDER BY al.interactionstartdateltc, al.gen1) AS transferred_to_queue,
lag(al.queuename) OVER (PARTITION BY al.conversationid ORDER BY al.interactionstartdateltc, al.gen1) AS transferred_from_queue
FROM agent_legs al
)
SELECT l.conversationid,
(((l.conversationid)::text || '_'::text) || l.conversationleg) AS interactionid,
l.conversationstartdateltc,
l.conversationenddateltc,
l.direction,
l.mediatype,
l.conversationleg,
l.interactionstartdateltc,
l.queuename,
l.agentname,
CASE
WHEN (l.conversationleg = 1) THEN COALESCE(iv.ivr_time, (0)::numeric)
ELSE (0)::numeric
END AS ivr_time,
COALESCE(qw.queue_time, (0)::numeric) AS queue_time,
l.talk_time,
l.hold_time,
l.wrap_up_time,
CASE
WHEN (l.transferred_to_queue IS NOT NULL) THEN 1
ELSE 0
END AS transferred_flag,
l.transferred_to_queue,
l.transferred_from_queue,
l.ani,
l.dnis,
l.alert_time,
((l.talk_time + l.hold_time) + l.wrap_up_time) AS handle_time,
l.wrapupcode,
l.wrapupname,
l.disconnectiontype,
l.ttalkcomplete,
l.theldcomplete,
l.tacw,
l.tagentresponsetime,
l.nconsulttransferred,
l.nblindtransferred,
l.queueid,
l.userid,
l.divisionid,
l.remotedisplayable,
l.externaltag,
l.recordingexists,
l.tanswered,
l.tuserresponsetime,
l.nconsult,
l.ntransferred,
l.tactivecallback,
l.tactivecallbackcomplete
FROM ((legs_numbered l
LEFT JOIN queue_wait qw ON ((((l.conversationid)::text = (qw.conversationid)::text) AND (l.queueid = (qw.queueid)::text))))
LEFT JOIN ivr_time iv ON (((l.conversationid)::text = (iv.conversationid)::text)))
ORDER BY l.conversationstartdateltc DESC, l.conversationid, l.conversationleg
)