CREATE VIEW vwinteractionlegdatav2 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.purpose)::text = 'agent'::text) OR (((di.purpose)::text = 'acd'::text) AND ((di.segmenttype)::text = 'interact'::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 = 'acd'::text) AND ((seg.segmenttype)::text = 'interact'::text) AND (seg.queueid IS NOT NULL) AND (seg.conversationid IS NOT NULL)) THEN seg.segmenttime
ELSE NULL::numeric
END) OVER (PARTITION BY seg.conversationid, seg.conversationstartdate, seg.conversationstartdateltc, seg.queueid) AS queue_time,
sum(
CASE
WHEN (((seg.purpose)::text = 'ivr'::text) AND ((seg.segmenttype)::text = 'ivr'::text) AND (seg.conversationid IS NOT NULL)) THEN seg.segmenttime
ELSE NULL::numeric
END) OVER (PARTITION BY seg.conversationid, seg.conversationstartdate, seg.conversationstartdateltc) 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.queue_time,
enriched.ivr_time,
max(
CASE
WHEN ((enriched.mediatype)::text = 'callback'::text) THEN 1
ELSE 0
END) OVER (PARTITION BY enriched.conversationid, enriched.conversationstartdate, enriched.conversationstartdateltc, 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.conversationstartdateltc,
agent_seg.gen1,
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.queue_time) AS queue_time,
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.conversationstartdateltc, 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.conversationstartdateltc,
al.gen1,
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.queue_time,
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.conversationstartdateltc,
nl.gen1,
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.queue_time,
nl.ivr_time,
nl.queuename,
nl.agentname,
nl.wrapupname,
row_number() OVER (PARTITION BY nl.conversationid, nl.conversationstartdate, nl.conversationstartdateltc ORDER BY nl.interactionstartdateltc, nl.gen1) AS conversationleg,
lead(nl.queuename) OVER (PARTITION BY nl.conversationid, nl.conversationstartdate, nl.conversationstartdateltc ORDER BY nl.interactionstartdateltc, nl.gen1) AS transferred_to_queue,
lag(nl.queuename) OVER (PARTITION BY nl.conversationid, nl.conversationstartdate, nl.conversationstartdateltc ORDER BY nl.interactionstartdateltc, nl.gen1) AS transferred_from_queue
FROM named_legs nl
)
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(l.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_numbered l
)