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.segmenttime,
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.segmenttime,
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.segmenttime,
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 1
ELSE 0
END) OVER (PARTITION BY enriched.conversationid, enriched.conversationstartdate, enriched.gen1) AS leg_has_callback
FROM enriched
WHERE ((enriched.purpose)::text = 'agent'::text)
), agent_legs AS (
SELECT agent_seg.conversationid,
agent_seg.conversationstartdate,
agent_seg.gen1,
max(agent_seg.conversationstartdateltc) AS conversationstartdateltc,
max((agent_seg.queueid)::text) AS queueid,
max((agent_seg.userid)::text) AS userid,
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 = '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,
max(agent_seg.ivr_time) AS ivr_time
FROM agent_seg
WHERE ((agent_seg.leg_has_callback = 0) OR ((agent_seg.mediatype)::text = 'callback'::text))
GROUP BY agent_seg.conversationid, agent_seg.conversationstartdate, agent_seg.gen1
HAVING (sum(
CASE
WHEN ((agent_seg.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.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.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,
lead(nl.queuename) OVER (PARTITION BY nl.conversationid, nl.conversationstartdate ORDER BY nl.interactionstartdateltc, nl.gen1) AS transferred_to_queue,
lag(nl.queuename) OVER (PARTITION BY nl.conversationid, nl.conversationstartdate ORDER BY nl.interactionstartdateltc, nl.gen1) AS transferred_from_queue
FROM named_legs nl
), 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.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.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_numbered 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.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,
l.conversationstartdate
FROM legs_with_queue_time l
WHERE (l.queue_match_rank = 1)
)