public.vwinteractionlegdatabuild

public.vwinteractionlegdatabuild

Description

Build query for the interactionlegdata table: one row per Leg (agent participant that handled a Conversation, its outbound voice calls excepted, or a user whose session carries a queue), one row per Dial (an outbound voice session placed by an agent or user, connected or not) and one row per Queue Entry no agent answered, computed from detailedinteractiondata and keyed by (conversationid, conversationstartdate). The Interaction job selects one UTC day of conversationstartdate at a time from it; consumers read the interactionlegdata table directly instead.

Table Definition
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)))
)

Columns

Name Type Default Nullable Children Parents Comment
conversationid varchar(50) true
interactionid text true
conversationstartdateltc timestamp without time zone true
conversationenddateltc timestamp without time zone true
direction text true
mediatype text true
conversationleg bigint true
interactionstartdateltc timestamp without time zone true
queuename varchar(255) true
agentname varchar(200) true
ivr_time numeric true
queue_time numeric true
talk_time numeric true
hold_time numeric true
wrap_up_time numeric true
transferred_flag integer true
transferred_to_queue varchar true
transferred_from_queue varchar true
ani text true
dnis text true
alert_time numeric true
handle_time numeric true
wrapupcode text true
wrapupname varchar(255) true
disconnectiontype text true
ttalkcomplete numeric true
theldcomplete numeric true
tacw numeric true
tagentresponsetime numeric true
nconsulttransferred bigint true
nblindtransferred bigint true
queueid text true
userid text true
divisionid text true
remotedisplayable text true
externaltag text true
recordingexists integer true
tanswered numeric true
tuserresponsetime numeric true
nconsult bigint true
ntransferred bigint true
tactivecallback numeric true
tactivecallbackcomplete numeric true
conversationstartdate timestamp without time zone true
callback_time numeric true
sessiondirection text true
queuestartdate timestamp without time zone true
queuestartdateltc timestamp without time zone true
agent_to_agent_flag integer true
abandoned integer true
abandon_time numeric(20,2) true
dialing_time numeric true
contacting_time numeric true
dial_attempt bigint true
dial_connected bigint true
callback_interactionid text true
participantpurpose varchar(20) true
interactionenddateltc timestamp without time zone true
conversationenddate timestamp without time zone true
interactionstartdate timestamp without time zone true
interactionenddate timestamp without time zone true

Referenced Tables

Name Columns Comment Type
public.detailedinteractiondata 154 Conversation Detailed Data BASE TABLE
enriched 0
agent_seg.segmentenddate 0
agent_seg 0
agent_cover 0
dial_grouped 0
agent_legs 0
dial_sessions 0
all_legs 0
public.queuedetails 25 Queue Lookup data BASE TABLE
public.vwuserdetail 18 User details with manager and division context VIEW
public.wrapupdetails 7 Wrap Up Code Details Lookup Up Data BASE TABLE
named_legs 0
legs_numbered 0
legs_linked 0
legs_chained 0
LATERAL 0
legs_with_queue_time 0
unanswered_entries 0