CREATE VIEW vwinteractionlegdatabuild AS (
WITH seg AS (
SELECT di.conversationid,
di.conversationstartdate,
di.conversationstartdateltc,
di.conversationenddateltc,
di.originaldirection,
di.mediatype,
di.purpose,
di.segmenttype,
di.queueid,
di.userid,
di.segmentstartdateltc,
di.segmentenddateltc,
di.segmenttime,
di.keyid,
di.segmentstartdate,
COALESCE(di.segmentenddate, di.segmentstartdate) AS segmentenddate,
di.ani,
di.dnis,
di.wrapupcode,
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,
CASE
WHEN ((di.purpose)::text = 'agent'::text) THEN (split_part((di.gencode)::text, '|'::text, 1))::integer
ELSE NULL::integer
END AS gen1
FROM detailedinteractiondata di
WHERE ((di.conversationid IS NOT NULL) AND (((di.purpose)::text = 'agent'::text) OR (((di.purpose)::text = 'ivr'::text) AND ((di.segmenttype)::text = 'ivr'::text))))
), enriched AS (
SELECT seg.conversationid,
seg.conversationstartdate,
seg.conversationstartdateltc,
seg.conversationenddateltc,
seg.originaldirection,
seg.mediatype,
seg.purpose,
seg.segmenttype,
seg.queueid,
seg.userid,
seg.segmentstartdateltc,
seg.segmentenddateltc,
seg.segmenttime,
seg.keyid,
seg.segmentstartdate,
seg.segmentenddate,
seg.ani,
seg.dnis,
seg.wrapupcode,
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,
sum(
CASE
WHEN (((seg.purpose)::text = 'ivr'::text) AND ((seg.segmenttype)::text = 'ivr'::text)) THEN seg.segmenttime
ELSE NULL::numeric
END) OVER (PARTITION BY seg.conversationid, seg.conversationstartdate) AS ivr_time
FROM seg
), agent_seg AS (
SELECT enriched.conversationid,
enriched.conversationstartdate,
enriched.conversationstartdateltc,
enriched.conversationenddateltc,
enriched.originaldirection,
enriched.mediatype,
enriched.purpose,
enriched.segmenttype,
enriched.queueid,
enriched.userid,
enriched.segmentstartdateltc,
enriched.segmentenddateltc,
enriched.segmenttime,
enriched.keyid,
enriched.segmentstartdate,
enriched.segmentenddate,
enriched.ani,
enriched.dnis,
enriched.wrapupcode,
enriched.disconnectiontype,
enriched.ttalkcomplete,
enriched.theldcomplete,
enriched.tacw,
enriched.tagentresponsetime,
enriched.nconsulttransferred,
enriched.nblindtransferred,
enriched.tanswered,
enriched.tuserresponsetime,
enriched.nconsult,
enriched.ntransferred,
enriched.tactivecallback,
enriched.tactivecallbackcomplete,
enriched.divisionid,
enriched.remotedisplayable,
enriched.externaltag,
enriched.recordingexists,
enriched.gen1,
enriched.ivr_time,
max(
CASE
WHEN ((enriched.mediatype)::text <> 'callback'::text) THEN enriched.segmentenddate
ELSE NULL::timestamp without time zone
END) OVER (PARTITION BY enriched.conversationid, enriched.conversationstartdate, enriched.gen1 ORDER BY enriched.segmentstartdate, enriched.keyid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS voice_prev,
max(
CASE
WHEN (((enriched.mediatype)::text <> 'callback'::text) OR ((enriched.segmenttype)::text = 'interact'::text)) THEN enriched.segmentenddate
ELSE NULL::timestamp without time zone
END) OVER (PARTITION BY enriched.conversationid, enriched.conversationstartdate, enriched.gen1 ORDER BY enriched.segmentstartdate, enriched.keyid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS interact_prev,
max(
CASE
WHEN (((enriched.mediatype)::text <> 'callback'::text) OR ((enriched.segmenttype)::text = 'hold'::text)) THEN enriched.segmentenddate
ELSE NULL::timestamp without time zone
END) OVER (PARTITION BY enriched.conversationid, enriched.conversationstartdate, enriched.gen1 ORDER BY enriched.segmentstartdate, enriched.keyid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS hold_prev,
max(
CASE
WHEN (((enriched.mediatype)::text <> 'callback'::text) OR ((enriched.segmenttype)::text = 'wrapup'::text)) THEN enriched.segmentenddate
ELSE NULL::timestamp without time zone
END) OVER (PARTITION BY enriched.conversationid, enriched.conversationstartdate, enriched.gen1 ORDER BY enriched.segmentstartdate, enriched.keyid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS wrapup_prev
FROM enriched
WHERE ((enriched.purpose)::text = 'agent'::text)
), agent_cover AS (
SELECT agent_seg.conversationid,
agent_seg.conversationstartdate,
agent_seg.conversationstartdateltc,
agent_seg.conversationenddateltc,
agent_seg.originaldirection,
agent_seg.mediatype,
agent_seg.purpose,
agent_seg.segmenttype,
agent_seg.queueid,
agent_seg.userid,
agent_seg.segmentstartdateltc,
agent_seg.segmentenddateltc,
agent_seg.segmenttime,
agent_seg.keyid,
agent_seg.segmentstartdate,
agent_seg.segmentenddate,
agent_seg.ani,
agent_seg.dnis,
agent_seg.wrapupcode,
agent_seg.disconnectiontype,
agent_seg.ttalkcomplete,
agent_seg.theldcomplete,
agent_seg.tacw,
agent_seg.tagentresponsetime,
agent_seg.nconsulttransferred,
agent_seg.nblindtransferred,
agent_seg.tanswered,
agent_seg.tuserresponsetime,
agent_seg.nconsult,
agent_seg.ntransferred,
agent_seg.tactivecallback,
agent_seg.tactivecallbackcomplete,
agent_seg.divisionid,
agent_seg.remotedisplayable,
agent_seg.externaltag,
agent_seg.recordingexists,
agent_seg.gen1,
agent_seg.ivr_time,
agent_seg.voice_prev,
agent_seg.interact_prev,
agent_seg.hold_prev,
agent_seg.wrapup_prev,
CASE
WHEN ((agent_seg.mediatype)::text = 'callback'::text) THEN EXTRACT(epoch FROM (agent_seg.segmentenddate - agent_seg.segmentstartdate))
ELSE (0)::numeric
END AS callback_len,
CASE
WHEN ((agent_seg.mediatype)::text <> 'callback'::text) THEN GREATEST((0)::numeric, EXTRACT(epoch FROM (agent_seg.segmentenddate - GREATEST(agent_seg.segmentstartdate, COALESCE(agent_seg.voice_prev, agent_seg.segmentstartdate)))))
ELSE (0)::numeric
END AS voice_new,
CASE
WHEN (((agent_seg.mediatype)::text <> 'callback'::text) OR ((agent_seg.segmenttype)::text = 'interact'::text)) THEN GREATEST((0)::numeric, EXTRACT(epoch FROM (agent_seg.segmentenddate - GREATEST(agent_seg.segmentstartdate, COALESCE(agent_seg.interact_prev, agent_seg.segmentstartdate)))))
ELSE (0)::numeric
END AS interact_new,
CASE
WHEN (((agent_seg.mediatype)::text <> 'callback'::text) OR ((agent_seg.segmenttype)::text = 'hold'::text)) THEN GREATEST((0)::numeric, EXTRACT(epoch FROM (agent_seg.segmentenddate - GREATEST(agent_seg.segmentstartdate, COALESCE(agent_seg.hold_prev, agent_seg.segmentstartdate)))))
ELSE (0)::numeric
END AS hold_new,
CASE
WHEN (((agent_seg.mediatype)::text <> 'callback'::text) OR ((agent_seg.segmenttype)::text = 'wrapup'::text)) THEN GREATEST((0)::numeric, EXTRACT(epoch FROM (agent_seg.segmentenddate - GREATEST(agent_seg.segmentstartdate, COALESCE(agent_seg.wrapup_prev, agent_seg.segmentstartdate)))))
ELSE (0)::numeric
END AS wrapup_new
FROM agent_seg
), agent_legs AS (
SELECT agent_cover.conversationid,
agent_cover.conversationstartdate,
agent_cover.gen1,
max(agent_cover.conversationstartdateltc) AS conversationstartdateltc,
max((agent_cover.queueid)::text) AS queueid,
max((agent_cover.userid)::text) AS userid,
max(agent_cover.conversationenddateltc) AS conversationenddateltc,
max((agent_cover.originaldirection)::text) AS direction,
CASE
WHEN (max(
CASE
WHEN ((agent_cover.mediatype)::text = 'callback'::text) THEN 1
ELSE 0
END) = 1) THEN 'callback'::text
ELSE max((agent_cover.mediatype)::text)
END AS mediatype,
min(
CASE
WHEN ((agent_cover.segmenttype)::text = 'interact'::text) THEN agent_cover.segmentstartdateltc
ELSE NULL::timestamp without time zone
END) AS interactionstartdateltc,
max(
CASE
WHEN (((agent_cover.segmenttype)::text = ANY ((ARRAY['interact'::character varying, 'hold'::character varying])::text[])) AND ((agent_cover.mediatype)::text <> 'callback'::text)) THEN agent_cover.segmentenddateltc
ELSE NULL::timestamp without time zone
END) AS interactionenddateltc,
round((sum(
CASE
WHEN ((agent_cover.segmenttype)::text = 'interact'::text) THEN agent_cover.segmenttime
ELSE (0)::numeric
END) - sum(((
CASE
WHEN ((agent_cover.segmenttype)::text = 'interact'::text) THEN agent_cover.callback_len
ELSE (0)::numeric
END + agent_cover.voice_new) - agent_cover.interact_new))), 2) AS talk_time,
round((sum(
CASE
WHEN ((agent_cover.segmenttype)::text = 'hold'::text) THEN agent_cover.segmenttime
ELSE (0)::numeric
END) - sum(((
CASE
WHEN ((agent_cover.segmenttype)::text = 'hold'::text) THEN agent_cover.callback_len
ELSE (0)::numeric
END + agent_cover.voice_new) - agent_cover.hold_new))), 2) AS hold_time,
round((sum(
CASE
WHEN ((agent_cover.segmenttype)::text = 'wrapup'::text) THEN agent_cover.segmenttime
ELSE (0)::numeric
END) - sum(((
CASE
WHEN ((agent_cover.segmenttype)::text = 'wrapup'::text) THEN agent_cover.callback_len
ELSE (0)::numeric
END + agent_cover.voice_new) - agent_cover.wrapup_new))), 2) AS wrap_up_time,
sum(
CASE
WHEN ((agent_cover.segmenttype)::text = 'alert'::text) THEN agent_cover.segmenttime
ELSE (0)::numeric
END) AS alert_time,
max((agent_cover.ani)::text) AS ani,
max((agent_cover.dnis)::text) AS dnis,
max((
CASE
WHEN ((agent_cover.segmenttype)::text = 'wrapup'::text) THEN agent_cover.wrapupcode
ELSE NULL::character varying
END)::text) AS wrapupcode,
max((
CASE
WHEN ((agent_cover.segmenttype)::text = 'interact'::text) THEN agent_cover.disconnectiontype
ELSE NULL::character varying
END)::text) AS disconnectiontype,
sum(COALESCE(agent_cover.ttalkcomplete, (0)::numeric)) AS ttalkcomplete,
sum(COALESCE(agent_cover.theldcomplete, (0)::numeric)) AS theldcomplete,
sum(COALESCE(agent_cover.tacw, (0)::numeric)) AS tacw,
sum(COALESCE(agent_cover.tagentresponsetime, (0)::numeric)) AS tagentresponsetime,
sum(COALESCE(agent_cover.nconsulttransferred, 0)) AS nconsulttransferred,
sum(COALESCE(agent_cover.nblindtransferred, 0)) AS nblindtransferred,
sum(COALESCE(agent_cover.tanswered, (0)::numeric)) AS tanswered,
sum(COALESCE(agent_cover.tuserresponsetime, (0)::numeric)) AS tuserresponsetime,
sum(COALESCE(agent_cover.nconsult, 0)) AS nconsult,
sum(COALESCE(agent_cover.ntransferred, 0)) AS ntransferred,
sum(COALESCE(agent_cover.tactivecallback, (0)::numeric)) AS tactivecallback,
sum(COALESCE(agent_cover.tactivecallbackcomplete, (0)::numeric)) AS tactivecallbackcomplete,
max((agent_cover.divisionid)::text) AS divisionid,
max((agent_cover.remotedisplayable)::text) AS remotedisplayable,
max((agent_cover.externaltag)::text) AS externaltag,
max(
CASE
WHEN (agent_cover.recordingexists = '1'::"bit") THEN 1
ELSE 0
END) AS recordingexists,
max(agent_cover.ivr_time) AS ivr_time
FROM agent_cover
GROUP BY agent_cover.conversationid, agent_cover.conversationstartdate, agent_cover.gen1
HAVING (sum(
CASE
WHEN ((agent_cover.segmenttype)::text = 'interact'::text) THEN 1
ELSE 0
END) > 0)
), named_legs AS (
SELECT al.conversationid,
al.conversationstartdate,
al.gen1,
al.conversationstartdateltc,
al.queueid,
al.userid,
al.conversationenddateltc,
al.direction,
al.mediatype,
al.interactionstartdateltc,
al.interactionenddateltc,
al.talk_time,
al.hold_time,
al.wrap_up_time,
al.alert_time,
al.ani,
al.dnis,
al.wrapupcode,
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,
al.ivr_time,
qd.name AS queuename,
ud.name AS agentname,
wd.name AS wrapupname
FROM (((agent_legs al
LEFT JOIN queuedetails qd ON (((qd.id)::text = al.queueid)))
LEFT JOIN vwuserdetail ud ON (((ud.id)::text = al.userid)))
LEFT JOIN wrapupdetails wd ON (((wd.id)::text = al.wrapupcode)))
), legs_numbered AS (
SELECT nl.conversationid,
nl.conversationstartdate,
nl.gen1,
nl.conversationstartdateltc,
nl.queueid,
nl.userid,
nl.conversationenddateltc,
nl.direction,
nl.mediatype,
nl.interactionstartdateltc,
nl.interactionenddateltc,
nl.talk_time,
nl.hold_time,
nl.wrap_up_time,
nl.alert_time,
nl.ani,
nl.dnis,
nl.wrapupcode,
nl.disconnectiontype,
nl.ttalkcomplete,
nl.theldcomplete,
nl.tacw,
nl.tagentresponsetime,
nl.nconsulttransferred,
nl.nblindtransferred,
nl.tanswered,
nl.tuserresponsetime,
nl.nconsult,
nl.ntransferred,
nl.tactivecallback,
nl.tactivecallbackcomplete,
nl.divisionid,
nl.remotedisplayable,
nl.externaltag,
nl.recordingexists,
nl.ivr_time,
nl.queuename,
nl.agentname,
nl.wrapupname,
row_number() OVER (PARTITION BY nl.conversationid, nl.conversationstartdate ORDER BY nl.interactionstartdateltc, nl.gen1) AS conversationleg,
CASE
WHEN (max(nl.interactionenddateltc) OVER (PARTITION BY nl.conversationid, nl.conversationstartdate ORDER BY nl.interactionstartdateltc, nl.gen1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) > nl.interactionenddateltc) THEN 1
ELSE 0
END AS consulted
FROM named_legs nl
), legs_chained AS (
SELECT l_1.conversationid,
l_1.conversationstartdate,
l_1.gen1,
l_1.conversationstartdateltc,
l_1.queueid,
l_1.userid,
l_1.conversationenddateltc,
l_1.direction,
l_1.mediatype,
l_1.interactionstartdateltc,
l_1.interactionenddateltc,
l_1.talk_time,
l_1.hold_time,
l_1.wrap_up_time,
l_1.alert_time,
l_1.ani,
l_1.dnis,
l_1.wrapupcode,
l_1.disconnectiontype,
l_1.ttalkcomplete,
l_1.theldcomplete,
l_1.tacw,
l_1.tagentresponsetime,
l_1.nconsulttransferred,
l_1.nblindtransferred,
l_1.tanswered,
l_1.tuserresponsetime,
l_1.nconsult,
l_1.ntransferred,
l_1.tactivecallback,
l_1.tactivecallbackcomplete,
l_1.divisionid,
l_1.remotedisplayable,
l_1.externaltag,
l_1.recordingexists,
l_1.ivr_time,
l_1.queuename,
l_1.agentname,
l_1.wrapupname,
l_1.conversationleg,
l_1.consulted,
CASE
WHEN ((l_1.consulted = 0) AND (l_1.ntransferred > 0)) THEN lead(l_1.queuename) OVER (PARTITION BY l_1.conversationid, l_1.conversationstartdate, l_1.consulted ORDER BY l_1.interactionstartdateltc, l_1.gen1)
ELSE NULL::character varying
END AS transferred_to_queue,
CASE
WHEN ((l_1.consulted = 0) AND (lag(l_1.ntransferred) OVER (PARTITION BY l_1.conversationid, l_1.conversationstartdate, l_1.consulted ORDER BY l_1.interactionstartdateltc, l_1.gen1) > 0)) THEN lag(l_1.queuename) OVER (PARTITION BY l_1.conversationid, l_1.conversationstartdate, l_1.consulted ORDER BY l_1.interactionstartdateltc, l_1.gen1)
ELSE NULL::character varying
END AS transferred_from_queue
FROM legs_numbered l_1
), legs_with_queue_time AS (
SELECT l_1.conversationid,
l_1.conversationstartdate,
l_1.gen1,
l_1.conversationstartdateltc,
l_1.queueid,
l_1.userid,
l_1.conversationenddateltc,
l_1.direction,
l_1.mediatype,
l_1.interactionstartdateltc,
l_1.interactionenddateltc,
l_1.talk_time,
l_1.hold_time,
l_1.wrap_up_time,
l_1.alert_time,
l_1.ani,
l_1.dnis,
l_1.wrapupcode,
l_1.disconnectiontype,
l_1.ttalkcomplete,
l_1.theldcomplete,
l_1.tacw,
l_1.tagentresponsetime,
l_1.nconsulttransferred,
l_1.nblindtransferred,
l_1.tanswered,
l_1.tuserresponsetime,
l_1.nconsult,
l_1.ntransferred,
l_1.tactivecallback,
l_1.tactivecallbackcomplete,
l_1.divisionid,
l_1.remotedisplayable,
l_1.externaltag,
l_1.recordingexists,
l_1.ivr_time,
l_1.queuename,
l_1.agentname,
l_1.wrapupname,
l_1.conversationleg,
l_1.consulted,
l_1.transferred_to_queue,
l_1.transferred_from_queue,
q.segmenttime AS acd_queue_time,
row_number() OVER (PARTITION BY l_1.conversationid, l_1.conversationstartdate, l_1.gen1 ORDER BY
CASE
WHEN (q.keyid IS NULL) THEN 1
ELSE 0
END, (abs(EXTRACT(epoch FROM (q.segmentenddateltc - l_1.interactionstartdateltc)))), q.keyid) AS queue_match_rank
FROM (legs_chained l_1
LEFT JOIN detailedinteractiondata q ON (((l_1.tanswered = (0)::numeric) AND ((q.conversationid)::text = (l_1.conversationid)::text) AND (q.conversationstartdate = l_1.conversationstartdate) AND ((q.queueid)::text = l_1.queueid) AND ((q.mediatype)::text = l_1.mediatype) AND ((q.purpose)::text = 'acd'::text) AND ((q.segmenttype)::text = 'interact'::text) AND ((q.segmentenddateltc >= (l_1.interactionstartdateltc - '00:00:01'::interval)) AND (q.segmentenddateltc <= (l_1.interactionstartdateltc + '00:00:01'::interval))))))
)
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(l.ivr_time, (0)::numeric)
ELSE (0)::numeric
END AS ivr_time,
COALESCE(NULLIF(l.tanswered, (0)::numeric), l.acd_queue_time, (0)::numeric) AS queue_time,
l.talk_time,
l.hold_time,
l.wrap_up_time,
CASE
WHEN (l.ntransferred > 0) 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,
l.conversationstartdate
FROM legs_with_queue_time l
WHERE (l.queue_match_rank = 1)
)