CREATE VIEW vwinteractionlegdatabuild AS (
WITH seg AS (
SELECT di.conversationid,
di.conversationstartdate,
di.conversationstartdateltc,
di.conversationenddateltc,
di.conversationenddate,
di.originaldirection,
di.sessiondirection,
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.segmentenddate AS segmentenddateraw,
di.ani,
di.dnis,
di.wrapupcode,
di.disconnectiontype,
di.ttalkcomplete,
di.theldcomplete,
di.tacw,
di.tdialing,
di.tcontacting,
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 = ANY ((ARRAY['agent'::character varying, 'user'::character varying])::text[])) THEN (split_part((di.gencode)::text, '|'::text, 1))::integer
ELSE NULL::integer
END AS gen1,
CASE
WHEN ((di.purpose)::text = ANY ((ARRAY['agent'::character varying, 'user'::character varying])::text[])) THEN (split_part((di.gencode)::text, '|'::text, 2))::integer
ELSE NULL::integer
END AS gen2,
CASE
WHEN (((di.mediatype)::text = 'voice'::text) AND ((di.sessiondirection)::text = 'outbound'::text) AND ((di.purpose)::text = ANY ((ARRAY['agent'::character varying, 'user'::character varying])::text[]))) THEN 1
ELSE 0
END AS is_dial
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)) OR (((di.purpose)::text = 'user'::text) AND (di.queueid IS NOT NULL))))
), enriched AS (
SELECT seg.conversationid,
seg.conversationstartdate,
seg.conversationstartdateltc,
seg.conversationenddateltc,
seg.conversationenddate,
seg.originaldirection,
seg.sessiondirection,
seg.mediatype,
seg.purpose,
seg.segmenttype,
seg.queueid,
seg.userid,
seg.segmentstartdateltc,
seg.segmentenddateltc,
seg.segmenttime,
seg.keyid,
seg.segmentstartdate,
seg.segmentenddate,
seg.segmentenddateraw,
seg.ani,
seg.dnis,
seg.wrapupcode,
seg.disconnectiontype,
seg.ttalkcomplete,
seg.theldcomplete,
seg.tacw,
seg.tdialing,
seg.tcontacting,
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,
seg.gen2,
seg.is_dial,
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.conversationenddate,
enriched.originaldirection,
enriched.sessiondirection,
enriched.mediatype,
enriched.purpose,
enriched.segmenttype,
enriched.queueid,
enriched.userid,
enriched.segmentstartdateltc,
enriched.segmentenddateltc,
enriched.segmenttime,
enriched.keyid,
enriched.segmentstartdate,
enriched.segmentenddate,
enriched.segmentenddateraw,
enriched.ani,
enriched.dnis,
enriched.wrapupcode,
enriched.disconnectiontype,
enriched.ttalkcomplete,
enriched.theldcomplete,
enriched.tacw,
enriched.tdialing,
enriched.tcontacting,
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.gen2,
enriched.is_dial,
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,
first_value(
CASE
WHEN ((enriched.segmenttype)::text = 'interact'::text) THEN enriched.sessiondirection
ELSE NULL::character varying
END) OVER (PARTITION BY enriched.conversationid, enriched.conversationstartdate, enriched.gen1 ORDER BY
CASE
WHEN ((enriched.segmenttype)::text = 'interact'::text) THEN 0
ELSE 1
END, enriched.segmentstartdate, enriched.keyid) AS leg_sessiondirection,
min(
CASE
WHEN ((enriched.segmenttype)::text = 'interact'::text) THEN enriched.segmentstartdate
ELSE NULL::timestamp without time zone
END) OVER (PARTITION BY enriched.conversationid, enriched.conversationstartdate, enriched.gen1) AS first_interact_start
FROM enriched
WHERE (((enriched.purpose)::text = ANY ((ARRAY['agent'::character varying, 'user'::character varying])::text[])) AND (enriched.is_dial = 0))
), agent_cover AS (
SELECT agent_seg.conversationid,
agent_seg.conversationstartdate,
agent_seg.conversationstartdateltc,
agent_seg.conversationenddateltc,
agent_seg.conversationenddate,
agent_seg.originaldirection,
agent_seg.sessiondirection,
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.segmentenddateraw,
agent_seg.ani,
agent_seg.dnis,
agent_seg.wrapupcode,
agent_seg.disconnectiontype,
agent_seg.ttalkcomplete,
agent_seg.theldcomplete,
agent_seg.tacw,
agent_seg.tdialing,
agent_seg.tcontacting,
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.gen2,
agent_seg.is_dial,
agent_seg.ivr_time,
agent_seg.voice_prev,
agent_seg.interact_prev,
agent_seg.hold_prev,
agent_seg.wrapup_prev,
agent_seg.leg_sessiondirection,
agent_seg.first_interact_start,
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,
0 AS subleg,
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.conversationenddate) AS conversationenddate,
max((agent_cover.originaldirection)::text) AS direction,
max((agent_cover.leg_sessiondirection)::text) AS sessiondirection,
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.segmentstartdate
ELSE NULL::timestamp without time zone
END) AS interactionstartdate,
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 = 'alert'::text) AND (agent_cover.segmentstartdate <= agent_cover.first_interact_start)) THEN agent_cover.segmentstartdate
ELSE NULL::timestamp without time zone
END) AS answer_alert_start,
max(
CASE
WHEN ((agent_cover.segmenttype)::text = ANY ((ARRAY['interact'::character varying, 'hold'::character varying])::text[])) THEN agent_cover.segmentenddateltc
ELSE NULL::timestamp without time zone
END) AS interactionenddateltc,
max(agent_cover.segmentenddateltc) AS rowenddateltc,
max(agent_cover.segmentenddateraw) AS rowenddate,
COALESCE(sum(
CASE
WHEN ((agent_cover.mediatype)::text <> 'callback'::text) THEN agent_cover.ttalkcomplete
ELSE NULL::numeric
END), sum(
CASE
WHEN (((agent_cover.segmenttype)::text = 'interact'::text) AND ((agent_cover.mediatype)::text <> 'callback'::text)) THEN agent_cover.segmenttime
ELSE (0)::numeric
END)) AS talk_time,
COALESCE(sum(agent_cover.theldcomplete), 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,
COALESCE(sum(agent_cover.tacw), 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,
round((sum(agent_cover.interact_new) - sum(agent_cover.voice_new)), 2) AS callback_time,
sum(
CASE
WHEN ((agent_cover.segmenttype)::text = 'alert'::text) THEN agent_cover.segmenttime
ELSE (0)::numeric
END) AS alert_time,
(0)::numeric(20,2) AS dialing_time,
(0)::numeric(20,2) AS contacting_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,
NULL::integer AS dial_connected,
(max((agent_cover.purpose)::text))::character varying(20) AS participantpurpose
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)
), dial_grouped AS (
SELECT enriched.conversationid,
enriched.conversationstartdate,
enriched.gen1,
enriched.gen2 AS subleg,
max(enriched.conversationstartdateltc) AS conversationstartdateltc,
max((enriched.queueid)::text) AS queueid,
max((enriched.userid)::text) AS userid,
max(enriched.conversationenddateltc) AS conversationenddateltc,
max(enriched.conversationenddate) AS conversationenddate,
max((enriched.originaldirection)::text) AS direction,
'outbound'::text AS sessiondirection,
'voice'::text AS mediatype,
min(enriched.segmentstartdate) AS interactionstartdate,
min(enriched.segmentstartdateltc) AS interactionstartdateltc,
NULL::timestamp without time zone AS answer_alert_start,
max(
CASE
WHEN ((enriched.segmenttype)::text = ANY ((ARRAY['interact'::character varying, 'hold'::character varying])::text[])) THEN enriched.segmentenddateltc
ELSE NULL::timestamp without time zone
END) AS interactionenddateltc,
max(enriched.segmentenddateltc) AS rowenddateltc,
max(enriched.segmentenddateraw) AS rowenddate,
COALESCE(max(enriched.ttalkcomplete), sum(
CASE
WHEN ((enriched.segmenttype)::text = 'interact'::text) THEN enriched.segmenttime
ELSE (0)::numeric
END)) AS talk_time,
COALESCE(max(enriched.theldcomplete), sum(
CASE
WHEN ((enriched.segmenttype)::text = 'hold'::text) THEN enriched.segmenttime
ELSE (0)::numeric
END)) AS hold_time,
COALESCE(max(enriched.tacw), sum(
CASE
WHEN ((enriched.segmenttype)::text = 'wrapup'::text) THEN enriched.segmenttime
ELSE (0)::numeric
END)) AS wrap_up_time,
(0)::numeric(20,2) AS callback_time,
(0)::numeric(20,2) AS alert_time,
COALESCE(max(enriched.tdialing), sum(
CASE
WHEN ((enriched.segmenttype)::text = 'dialing'::text) THEN enriched.segmenttime
ELSE (0)::numeric
END)) AS dialing_time,
COALESCE(max(enriched.tcontacting), sum(
CASE
WHEN ((enriched.segmenttype)::text = 'contacting'::text) THEN enriched.segmenttime
ELSE (0)::numeric
END)) AS contacting_time,
max((enriched.ani)::text) AS ani,
max((enriched.dnis)::text) AS dnis,
max((
CASE
WHEN ((enriched.segmenttype)::text = 'wrapup'::text) THEN enriched.wrapupcode
ELSE NULL::character varying
END)::text) AS wrapupcode,
COALESCE(max((
CASE
WHEN ((enriched.segmenttype)::text = 'interact'::text) THEN enriched.disconnectiontype
ELSE NULL::character varying
END)::text), max((
CASE
WHEN ((enriched.segmenttype)::text = ANY ((ARRAY['dialing'::character varying, 'contacting'::character varying])::text[])) THEN enriched.disconnectiontype
ELSE NULL::character varying
END)::text)) AS disconnectiontype,
sum(COALESCE(enriched.ttalkcomplete, (0)::numeric)) AS ttalkcomplete,
sum(COALESCE(enriched.theldcomplete, (0)::numeric)) AS theldcomplete,
sum(COALESCE(enriched.tacw, (0)::numeric)) AS tacw,
sum(COALESCE(enriched.tagentresponsetime, (0)::numeric)) AS tagentresponsetime,
sum(COALESCE(enriched.nconsulttransferred, 0)) AS nconsulttransferred,
sum(COALESCE(enriched.nblindtransferred, 0)) AS nblindtransferred,
(0)::numeric(20,2) AS tanswered,
sum(COALESCE(enriched.tuserresponsetime, (0)::numeric)) AS tuserresponsetime,
sum(COALESCE(enriched.nconsult, 0)) AS nconsult,
sum(COALESCE(enriched.ntransferred, 0)) AS ntransferred,
(0)::numeric(20,2) AS tactivecallback,
(0)::numeric(20,2) AS tactivecallbackcomplete,
max((enriched.divisionid)::text) AS divisionid,
max((enriched.remotedisplayable)::text) AS remotedisplayable,
max((enriched.externaltag)::text) AS externaltag,
max(
CASE
WHEN (enriched.recordingexists = '1'::"bit") THEN 1
ELSE 0
END) AS recordingexists,
max(enriched.ivr_time) AS ivr_time,
min(enriched.segmentstartdate) AS dial_start,
max(enriched.segmentenddate) AS dial_end,
(max((enriched.purpose)::text))::character varying(20) AS participantpurpose
FROM enriched
WHERE (enriched.is_dial = 1)
GROUP BY enriched.conversationid, enriched.conversationstartdate, enriched.gen1, enriched.gen2
), dial_sessions AS (
SELECT d.conversationid,
d.conversationstartdate,
d.gen1,
d.subleg,
d.conversationstartdateltc,
d.queueid,
d.userid,
d.conversationenddateltc,
d.conversationenddate,
d.direction,
d.sessiondirection,
d.mediatype,
d.interactionstartdate,
d.interactionstartdateltc,
d.answer_alert_start,
d.interactionenddateltc,
d.rowenddateltc,
d.rowenddate,
d.talk_time,
d.hold_time,
d.wrap_up_time,
d.callback_time,
d.alert_time,
d.dialing_time,
d.contacting_time,
d.ani,
d.dnis,
d.wrapupcode,
d.disconnectiontype,
d.ttalkcomplete,
d.theldcomplete,
d.tacw,
d.tagentresponsetime,
d.nconsulttransferred,
d.nblindtransferred,
d.tanswered,
d.tuserresponsetime,
d.nconsult,
d.ntransferred,
d.tactivecallback,
d.tactivecallbackcomplete,
d.divisionid,
d.remotedisplayable,
d.externaltag,
d.recordingexists,
d.ivr_time,
d.dial_start,
d.dial_end,
d.participantpurpose,
( SELECT count(*) AS count
FROM ( SELECT min(c_1.segmentstartdate) AS cstart,
max(COALESCE(c_1.segmentenddate, c_1.segmentstartdate)) AS cend
FROM detailedinteractiondata c_1
WHERE (((c_1.conversationid)::text = (d.conversationid)::text) AND (c_1.conversationstartdate = d.conversationstartdate) AND ((c_1.purpose)::text = ANY ((ARRAY['customer'::character varying, 'external'::character varying])::text[])) AND ((c_1.mediatype)::text = 'voice'::text) AND ((c_1.sessiondirection)::text = 'outbound'::text) AND ((c_1.segmenttype)::text = 'interact'::text) AND (c_1.queueid IS NOT NULL))
GROUP BY (split_part((c_1.gencode)::text, '|'::text, 1)), (split_part((c_1.gencode)::text, '|'::text, 2))) c
WHERE ((c.cstart <= (d.dial_end + '00:00:02'::interval)) AND (c.cend >= (d.dial_start - '00:00:02'::interval)) AND (NOT (EXISTS ( SELECT 1
FROM detailedinteractiondata k
WHERE (((k.conversationid)::text = (d.conversationid)::text) AND (k.conversationstartdate = d.conversationstartdate) AND ((k.purpose)::text = ANY ((ARRAY['agent'::character varying, 'user'::character varying])::text[])) AND ((k.mediatype)::text = 'voice'::text) AND ((k.sessiondirection)::text = 'outbound'::text) AND (k.queueid IS NOT NULL) AND ((k.segmenttype)::text = ANY ((ARRAY['contacting'::character varying, 'dialing'::character varying])::text[])) AND (NOT ((split_part((k.gencode)::text, '|'::text, 1) = ((d.gen1)::character varying)::text) AND (split_part((k.gencode)::text, '|'::text, 2) = ((d.subleg)::character varying)::text))) AND (((k.segmentstartdate > d.dial_start) AND (k.segmentstartdate <= (c.cstart + '00:00:02'::interval))) OR ((d.dial_start > (c.cstart + '00:00:02'::interval)) AND (k.segmentstartdate > (c.cstart + '00:00:02'::interval)) AND (k.segmentstartdate < d.dial_start))))))))) AS dial_connected
FROM dial_grouped d
), all_legs AS (
SELECT agent_legs.conversationid,
agent_legs.conversationstartdate,
agent_legs.gen1,
agent_legs.subleg,
agent_legs.conversationstartdateltc,
agent_legs.queueid,
agent_legs.userid,
agent_legs.conversationenddateltc,
agent_legs.conversationenddate,
agent_legs.direction,
agent_legs.sessiondirection,
agent_legs.mediatype,
agent_legs.interactionstartdate,
agent_legs.interactionstartdateltc,
agent_legs.answer_alert_start,
agent_legs.interactionenddateltc,
agent_legs.rowenddateltc,
agent_legs.rowenddate,
agent_legs.talk_time,
agent_legs.hold_time,
agent_legs.wrap_up_time,
agent_legs.callback_time,
agent_legs.alert_time,
agent_legs.dialing_time,
agent_legs.contacting_time,
agent_legs.ani,
agent_legs.dnis,
agent_legs.wrapupcode,
agent_legs.disconnectiontype,
agent_legs.ttalkcomplete,
agent_legs.theldcomplete,
agent_legs.tacw,
agent_legs.tagentresponsetime,
agent_legs.nconsulttransferred,
agent_legs.nblindtransferred,
agent_legs.tanswered,
agent_legs.tuserresponsetime,
agent_legs.nconsult,
agent_legs.ntransferred,
agent_legs.tactivecallback,
agent_legs.tactivecallbackcomplete,
agent_legs.divisionid,
agent_legs.remotedisplayable,
agent_legs.externaltag,
agent_legs.recordingexists,
agent_legs.ivr_time,
agent_legs.dial_connected,
agent_legs.participantpurpose
FROM agent_legs
UNION ALL
SELECT dial_sessions.conversationid,
dial_sessions.conversationstartdate,
dial_sessions.gen1,
dial_sessions.subleg,
dial_sessions.conversationstartdateltc,
dial_sessions.queueid,
dial_sessions.userid,
dial_sessions.conversationenddateltc,
dial_sessions.conversationenddate,
dial_sessions.direction,
dial_sessions.sessiondirection,
dial_sessions.mediatype,
dial_sessions.interactionstartdate,
dial_sessions.interactionstartdateltc,
dial_sessions.answer_alert_start,
dial_sessions.interactionenddateltc,
dial_sessions.rowenddateltc,
dial_sessions.rowenddate,
dial_sessions.talk_time,
dial_sessions.hold_time,
dial_sessions.wrap_up_time,
dial_sessions.callback_time,
dial_sessions.alert_time,
dial_sessions.dialing_time,
dial_sessions.contacting_time,
dial_sessions.ani,
dial_sessions.dnis,
dial_sessions.wrapupcode,
dial_sessions.disconnectiontype,
dial_sessions.ttalkcomplete,
dial_sessions.theldcomplete,
dial_sessions.tacw,
dial_sessions.tagentresponsetime,
dial_sessions.nconsulttransferred,
dial_sessions.nblindtransferred,
dial_sessions.tanswered,
dial_sessions.tuserresponsetime,
dial_sessions.nconsult,
dial_sessions.ntransferred,
dial_sessions.tactivecallback,
dial_sessions.tactivecallbackcomplete,
dial_sessions.divisionid,
dial_sessions.remotedisplayable,
dial_sessions.externaltag,
dial_sessions.recordingexists,
dial_sessions.ivr_time,
dial_sessions.dial_connected,
dial_sessions.participantpurpose
FROM dial_sessions
), named_legs AS (
SELECT al.conversationid,
al.conversationstartdate,
al.gen1,
al.subleg,
al.conversationstartdateltc,
al.queueid,
al.userid,
al.conversationenddateltc,
al.conversationenddate,
al.direction,
al.sessiondirection,
al.mediatype,
al.interactionstartdate,
al.interactionstartdateltc,
al.answer_alert_start,
al.interactionenddateltc,
al.rowenddateltc,
al.rowenddate,
al.talk_time,
al.hold_time,
al.wrap_up_time,
al.callback_time,
al.alert_time,
al.dialing_time,
al.contacting_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,
al.dial_connected,
al.participantpurpose,
qd.name AS queuename,
ud.name AS agentname,
wd.name AS wrapupname
FROM (((all_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.subleg,
nl.conversationstartdateltc,
nl.queueid,
nl.userid,
nl.conversationenddateltc,
nl.conversationenddate,
nl.direction,
nl.sessiondirection,
nl.mediatype,
nl.interactionstartdate,
nl.interactionstartdateltc,
nl.answer_alert_start,
nl.interactionenddateltc,
nl.rowenddateltc,
nl.rowenddate,
nl.talk_time,
nl.hold_time,
nl.wrap_up_time,
nl.callback_time,
nl.alert_time,
nl.dialing_time,
nl.contacting_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.dial_connected,
nl.participantpurpose,
nl.queuename,
nl.agentname,
nl.wrapupname,
row_number() OVER (PARTITION BY nl.conversationid, nl.conversationstartdate ORDER BY nl.interactionstartdateltc, nl.gen1, nl.subleg) AS conversationleg,
CASE
WHEN (max(nl.interactionenddateltc) OVER (PARTITION BY nl.conversationid, nl.conversationstartdate ORDER BY nl.interactionstartdateltc, nl.gen1, nl.subleg ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) > nl.interactionenddateltc) THEN 1
ELSE 0
END AS consulted,
CASE
WHEN (nl.subleg > 0) THEN row_number() OVER (PARTITION BY nl.conversationid, nl.conversationstartdate, nl.gen1,
CASE
WHEN (nl.subleg > 0) THEN 1
ELSE 0
END ORDER BY nl.interactionstartdate, nl.subleg)
ELSE NULL::bigint
END AS dial_attempt
FROM named_legs nl
), legs_linked AS (
SELECT l.conversationid,
l.conversationstartdate,
l.gen1,
l.subleg,
l.conversationstartdateltc,
l.queueid,
l.userid,
l.conversationenddateltc,
l.conversationenddate,
l.direction,
l.sessiondirection,
l.mediatype,
l.interactionstartdate,
l.interactionstartdateltc,
l.answer_alert_start,
l.interactionenddateltc,
l.rowenddateltc,
l.rowenddate,
l.talk_time,
l.hold_time,
l.wrap_up_time,
l.callback_time,
l.alert_time,
l.dialing_time,
l.contacting_time,
l.ani,
l.dnis,
l.wrapupcode,
l.disconnectiontype,
l.ttalkcomplete,
l.theldcomplete,
l.tacw,
l.tagentresponsetime,
l.nconsulttransferred,
l.nblindtransferred,
l.tanswered,
l.tuserresponsetime,
l.nconsult,
l.ntransferred,
l.tactivecallback,
l.tactivecallbackcomplete,
l.divisionid,
l.remotedisplayable,
l.externaltag,
l.recordingexists,
l.ivr_time,
l.dial_connected,
l.participantpurpose,
l.queuename,
l.agentname,
l.wrapupname,
l.conversationleg,
l.consulted,
l.dial_attempt,
CASE
WHEN (l.subleg > 0) THEN max(
CASE
WHEN ((l.subleg = 0) AND (l.mediatype = 'callback'::text)) THEN l.conversationleg
ELSE NULL::bigint
END) OVER (PARTITION BY l.conversationid, l.conversationstartdate, l.gen1)
ELSE NULL::bigint
END AS callback_leg
FROM legs_numbered l
), legs_chained AS (
SELECT l.conversationid,
l.conversationstartdate,
l.gen1,
l.subleg,
l.conversationstartdateltc,
l.queueid,
l.userid,
l.conversationenddateltc,
l.conversationenddate,
l.direction,
l.sessiondirection,
l.mediatype,
l.interactionstartdate,
l.interactionstartdateltc,
l.answer_alert_start,
l.interactionenddateltc,
l.rowenddateltc,
l.rowenddate,
l.talk_time,
l.hold_time,
l.wrap_up_time,
l.callback_time,
l.alert_time,
l.dialing_time,
l.contacting_time,
l.ani,
l.dnis,
l.wrapupcode,
l.disconnectiontype,
l.ttalkcomplete,
l.theldcomplete,
l.tacw,
l.tagentresponsetime,
l.nconsulttransferred,
l.nblindtransferred,
l.tanswered,
l.tuserresponsetime,
l.nconsult,
l.ntransferred,
l.tactivecallback,
l.tactivecallbackcomplete,
l.divisionid,
l.remotedisplayable,
l.externaltag,
l.recordingexists,
l.ivr_time,
l.dial_connected,
l.participantpurpose,
l.queuename,
l.agentname,
l.wrapupname,
l.conversationleg,
l.consulted,
l.dial_attempt,
l.callback_leg,
CASE
WHEN ((l.consulted = 0) AND (l.ntransferred > 0)) THEN lead(l.queuename) OVER (PARTITION BY l.conversationid, l.conversationstartdate, l.consulted ORDER BY l.interactionstartdateltc, l.gen1, l.subleg)
ELSE NULL::character varying
END AS transferred_to_queue,
CASE
WHEN ((l.consulted = 0) AND (lag(l.ntransferred) OVER (PARTITION BY l.conversationid, l.conversationstartdate, l.consulted ORDER BY l.interactionstartdateltc, l.gen1, l.subleg) > 0)) THEN lag(l.queuename) OVER (PARTITION BY l.conversationid, l.conversationstartdate, l.consulted ORDER BY l.interactionstartdateltc, l.gen1, l.subleg)
ELSE NULL::character varying
END AS transferred_from_queue
FROM legs_linked l
), legs_with_queue_time AS (
SELECT l.conversationid,
l.conversationstartdate,
l.gen1,
l.subleg,
l.conversationstartdateltc,
l.queueid,
l.userid,
l.conversationenddateltc,
l.conversationenddate,
l.direction,
l.sessiondirection,
l.mediatype,
l.interactionstartdate,
l.interactionstartdateltc,
l.answer_alert_start,
l.interactionenddateltc,
l.rowenddateltc,
l.rowenddate,
l.talk_time,
l.hold_time,
l.wrap_up_time,
l.callback_time,
l.alert_time,
l.dialing_time,
l.contacting_time,
l.ani,
l.dnis,
l.wrapupcode,
l.disconnectiontype,
l.ttalkcomplete,
l.theldcomplete,
l.tacw,
l.tagentresponsetime,
l.nconsulttransferred,
l.nblindtransferred,
l.tanswered,
l.tuserresponsetime,
l.nconsult,
l.ntransferred,
l.tactivecallback,
l.tactivecallbackcomplete,
l.divisionid,
l.remotedisplayable,
l.externaltag,
l.recordingexists,
l.ivr_time,
l.dial_connected,
l.participantpurpose,
l.queuename,
l.agentname,
l.wrapupname,
l.conversationleg,
l.consulted,
l.dial_attempt,
l.callback_leg,
l.transferred_to_queue,
l.transferred_from_queue,
q.segmenttime AS acd_queue_time,
q.segmentstartdate AS acd_queue_start,
q.segmentstartdateltc AS acd_queue_startltc,
row_number() OVER (PARTITION BY l.conversationid, l.conversationstartdate, l.gen1, l.subleg ORDER BY
CASE
WHEN (q.keyid IS NULL) THEN 1
ELSE 0
END, q.segmentstartdate DESC, q.keyid) AS queue_match_rank
FROM (legs_chained l
LEFT JOIN detailedinteractiondata q ON (((l.subleg = 0) AND ((l.participantpurpose)::text = 'agent'::text) AND ((q.conversationid)::text = (l.conversationid)::text) AND (q.conversationstartdate = l.conversationstartdate) AND ((q.mediatype)::text = l.mediatype) AND ((q.purpose)::text = 'acd'::text) AND ((q.segmenttype)::text = 'interact'::text) AND (((l.answer_alert_start IS NOT NULL) AND ((l.answer_alert_start >= (q.segmentstartdate - '00:00:01'::interval)) AND (l.answer_alert_start <= (COALESCE(q.segmentenddate, q.segmentstartdate) + '00:00:01'::interval)))) OR ((l.answer_alert_start IS NULL) AND ((COALESCE(q.segmentenddate, q.segmentstartdate) >= (l.interactionstartdate - '00:00:01'::interval)) AND (COALESCE(q.segmentenddate, q.segmentstartdate) <= (l.interactionstartdate + '00:00:01'::interval))))))))
), unanswered_entries AS (
SELECT q.conversationid,
q.conversationstartdate,
q.conversationstartdateltc,
q.conversationenddateltc,
q.conversationenddate,
q.originaldirection,
q.sessiondirection,
q.mediatype,
q.queueid,
q.segmentstartdate,
q.segmentstartdateltc,
q.segmentenddateltc,
q.segmentenddate,
q.segmenttime,
q.disconnectiontype,
q.tabandon,
q.ani,
q.dnis,
q.divisionid,
q.remotedisplayable,
q.externaltag,
row_number() OVER (PARTITION BY q.conversationid, q.conversationstartdate ORDER BY q.segmentstartdate, q.keyid) AS entry_number,
( SELECT (count(DISTINCT
CASE
WHEN ((((a.purpose)::text = 'agent'::text) OR (((a.purpose)::text = 'user'::text) AND (a.queueid IS NOT NULL))) AND ((a.segmenttype)::text = 'interact'::text) AND (NOT (((a.mediatype)::text = 'voice'::text) AND ((a.sessiondirection)::text = 'outbound'::text)))) THEN split_part((a.gencode)::text, '|'::text, 1)
ELSE NULL::text
END) + count(DISTINCT
CASE
WHEN (((a.mediatype)::text = 'voice'::text) AND ((a.sessiondirection)::text = 'outbound'::text) AND (((a.purpose)::text = 'agent'::text) OR (((a.purpose)::text = 'user'::text) AND (a.queueid IS NOT NULL)))) THEN ((split_part((a.gencode)::text, '|'::text, 1) || '|'::text) || split_part((a.gencode)::text, '|'::text, 2))
ELSE NULL::text
END))
FROM detailedinteractiondata a
WHERE (((a.conversationid)::text = (q.conversationid)::text) AND (a.conversationstartdate = q.conversationstartdate))) AS leg_rows,
( SELECT sum(i.segmenttime) AS sum
FROM detailedinteractiondata i
WHERE (((i.conversationid)::text = (q.conversationid)::text) AND (i.conversationstartdate = q.conversationstartdate) AND ((i.purpose)::text = 'ivr'::text) AND ((i.segmenttype)::text = 'ivr'::text))) AS ivr_time
FROM detailedinteractiondata q
WHERE ((q.conversationid IS NOT NULL) AND ((q.purpose)::text = 'acd'::text) AND (q.noffered IS NOT NULL) AND (NOT (EXISTS ( SELECT 1
FROM (detailedinteractiondata g
CROSS JOIN LATERAL ( SELECT max(al.segmentstartdate) AS alert_start
FROM detailedinteractiondata al
WHERE (((al.conversationid)::text = (g.conversationid)::text) AND (al.conversationstartdate = g.conversationstartdate) AND ((al.purpose)::text = 'agent'::text) AND ((al.segmenttype)::text = 'alert'::text) AND (NOT (((al.mediatype)::text = 'voice'::text) AND ((al.sessiondirection)::text = 'outbound'::text))) AND (split_part((al.gencode)::text, '|'::text, 1) = split_part((g.gencode)::text, '|'::text, 1)) AND (al.segmentstartdate <= g.segmentstartdate))) a)
WHERE (((g.conversationid)::text = (q.conversationid)::text) AND (g.conversationstartdate = q.conversationstartdate) AND ((g.purpose)::text = 'agent'::text) AND ((g.segmenttype)::text = 'interact'::text) AND ((g.mediatype)::text = (q.mediatype)::text) AND (NOT (((g.mediatype)::text = 'voice'::text) AND ((g.sessiondirection)::text = 'outbound'::text))) AND (NOT (EXISTS ( SELECT 1
FROM detailedinteractiondata e
WHERE (((e.conversationid)::text = (g.conversationid)::text) AND (e.conversationstartdate = g.conversationstartdate) AND ((e.purpose)::text = 'agent'::text) AND ((e.segmenttype)::text = 'interact'::text) AND (NOT (((e.mediatype)::text = 'voice'::text) AND ((e.sessiondirection)::text = 'outbound'::text))) AND (split_part((e.gencode)::text, '|'::text, 1) = split_part((g.gencode)::text, '|'::text, 1)) AND (e.segmentstartdate < g.segmentstartdate))))) AND (((a.alert_start IS NOT NULL) AND ((a.alert_start >= (q.segmentstartdate - '00:00:01'::interval)) AND (a.alert_start <= (COALESCE(q.segmentenddate, q.segmentstartdate) + '00:00:01'::interval)))) OR ((a.alert_start IS NULL) AND ((g.segmentstartdate >= (COALESCE(q.segmentenddate, q.segmentstartdate) - '00:00:01'::interval)) AND (g.segmentstartdate <= (COALESCE(q.segmentenddate, q.segmentstartdate) + '00:00:01'::interval))))) AND (NOT (EXISTS ( SELECT 1
FROM detailedinteractiondata q2
WHERE (((q2.conversationid)::text = (q.conversationid)::text) AND (q2.conversationstartdate = q.conversationstartdate) AND ((q2.purpose)::text = 'acd'::text) AND ((q2.segmenttype)::text = 'interact'::text) AND ((q2.mediatype)::text = (q.mediatype)::text) AND ((q2.segmentstartdate > q.segmentstartdate) OR ((q2.segmentstartdate = q.segmentstartdate) AND ((q2.keyid)::text < (q.keyid)::text))) AND (((a.alert_start IS NOT NULL) AND ((a.alert_start >= (q2.segmentstartdate - '00:00:01'::interval)) AND (a.alert_start <= (COALESCE(q2.segmentenddate, q2.segmentstartdate) + '00:00:01'::interval)))) OR ((a.alert_start IS NULL) AND ((g.segmentstartdate >= (COALESCE(q2.segmentenddate, q2.segmentstartdate) - '00:00:01'::interval)) AND (g.segmentstartdate <= (COALESCE(q2.segmentenddate, q2.segmentstartdate) + '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) + l.callback_time) + l.dialing_time) + l.contacting_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,
l.callback_time,
l.sessiondirection,
COALESCE(l.acd_queue_start,
CASE
WHEN (l.tanswered > (0)::numeric) THEN (l.interactionstartdate - ((l.tanswered)::double precision * '00:00:01'::interval))
ELSE NULL::timestamp without time zone
END) AS queuestartdate,
COALESCE(l.acd_queue_startltc,
CASE
WHEN (l.tanswered > (0)::numeric) THEN (l.interactionstartdateltc - ((l.tanswered)::double precision * '00:00:01'::interval))
ELSE NULL::timestamp without time zone
END) AS queuestartdateltc,
CASE
WHEN ((l.subleg = 0) AND (l.acd_queue_start IS NULL) AND (l.tanswered = (0)::numeric) AND ((l.conversationleg > 1) OR ((l.participantpurpose)::text = 'user'::text))) THEN 1
ELSE 0
END AS agent_to_agent_flag,
0 AS abandoned,
NULL::numeric(20,2) AS abandon_time,
l.dialing_time,
l.contacting_time,
l.dial_attempt,
l.dial_connected,
CASE
WHEN (l.callback_leg IS NOT NULL) THEN (((l.conversationid)::text || '_'::text) || l.callback_leg)
ELSE NULL::text
END AS callback_interactionid,
l.participantpurpose,
l.rowenddateltc AS interactionenddateltc,
l.conversationenddate,
l.interactionstartdate,
l.rowenddate AS interactionenddate
FROM legs_with_queue_time l
WHERE (l.queue_match_rank = 1)
UNION ALL
SELECT u.conversationid,
(((u.conversationid)::text || '_'::text) || (u.leg_rows + u.entry_number)) AS interactionid,
u.conversationstartdateltc,
u.conversationenddateltc,
u.originaldirection AS direction,
u.mediatype,
(u.leg_rows + u.entry_number) AS conversationleg,
NULL::timestamp without time zone AS interactionstartdateltc,
qd.name AS queuename,
NULL::character varying(200) AS agentname,
CASE
WHEN ((u.leg_rows + u.entry_number) = 1) THEN COALESCE(u.ivr_time, (0)::numeric)
ELSE (0)::numeric
END AS ivr_time,
u.segmenttime AS queue_time,
NULL::numeric(20,2) AS talk_time,
NULL::numeric(20,2) AS hold_time,
NULL::numeric(20,2) AS wrap_up_time,
0 AS transferred_flag,
NULL::character varying(255) AS transferred_to_queue,
NULL::character varying(255) AS transferred_from_queue,
u.ani,
u.dnis,
NULL::numeric(20,2) AS alert_time,
NULL::numeric(20,2) AS handle_time,
NULL::character varying(255) AS wrapupcode,
NULL::character varying(255) AS wrapupname,
u.disconnectiontype,
NULL::numeric(20,2) AS ttalkcomplete,
NULL::numeric(20,2) AS theldcomplete,
NULL::numeric(20,2) AS tacw,
NULL::numeric(20,2) AS tagentresponsetime,
NULL::integer AS nconsulttransferred,
NULL::integer AS nblindtransferred,
u.queueid,
NULL::character varying(50) AS userid,
u.divisionid,
u.remotedisplayable,
u.externaltag,
0 AS recordingexists,
NULL::numeric(20,2) AS tanswered,
NULL::numeric(20,2) AS tuserresponsetime,
NULL::integer AS nconsult,
NULL::integer AS ntransferred,
NULL::numeric(20,2) AS tactivecallback,
NULL::numeric(20,2) AS tactivecallbackcomplete,
u.conversationstartdate,
NULL::numeric(20,2) AS callback_time,
u.sessiondirection,
u.segmentstartdate AS queuestartdate,
u.segmentstartdateltc AS queuestartdateltc,
0 AS agent_to_agent_flag,
CASE
WHEN (u.tabandon IS NOT NULL) THEN 1
ELSE 0
END AS abandoned,
u.tabandon AS abandon_time,
NULL::numeric(20,2) AS dialing_time,
NULL::numeric(20,2) AS contacting_time,
NULL::integer AS dial_attempt,
NULL::integer AS dial_connected,
NULL::character varying(64) AS callback_interactionid,
NULL::character varying(20) AS participantpurpose,
u.segmentenddateltc AS interactionenddateltc,
u.conversationenddate,
NULL::timestamp without time zone AS interactionstartdate,
u.segmentenddate AS interactionenddate
FROM (unanswered_entries u
LEFT JOIN queuedetails qd ON (((qd.id)::text = (u.queueid)::text)))
)