public.vwoutboundattemptdatabuild

public.vwoutboundattemptdatabuild

Description

Build query for the outboundattemptdata table: one row per Attempt (a Conversation of an outbound campaign contact in which a call was placed, split where a scheduled callback came due) computed from detailedinteractiondata. The Interaction job selects one UTC day of conversationstartdate at a time from it; consumers read vwOutboundAttemptData instead.

Table Definition
CREATE VIEW vwoutboundattemptdatabuild AS (
 WITH seg AS NOT MATERIALIZED (
         SELECT di.conversationid,
            di.conversationstartdate,
            di.sessionid,
            di.purpose,
            di.mediatype,
            di.sessiondirection,
            di.segmenttype,
            di.segmentstartdate,
            di.segmentenddate,
            di.disconnectiontype,
            di.errorcode,
            di.sipresponsecodes,
            di.q850responsecodes,
            di.wrapupcode,
            di.callbackscheduledtime,
            di.outboundcampaignid,
            di.outboundcontactid,
            di.outboundcontactlistid,
            di.dispositionname,
            di.userid,
            di.queueid,
            di.noutboundconnected,
            di.ttalkcomplete,
            di.theldcomplete,
            di.tacw,
            di.thandle,
            di.tacd
           FROM detailedinteractiondata di
          WHERE ((di.outboundcampaignid IS NOT NULL) AND (di.conversationid IS NOT NULL) AND (di.sessionid IS NOT NULL))
        ), events AS (
         SELECT s.conversationid,
            s.conversationstartdate,
            s.sessionid,
            s.purpose,
            s.mediatype,
            s.sessiondirection,
            s.segmenttype,
            s.segmentstartdate,
            s.segmentenddate,
            s.disconnectiontype,
            s.errorcode,
            s.sipresponsecodes,
            s.q850responsecodes,
            s.wrapupcode,
            s.callbackscheduledtime,
            s.outboundcampaignid,
            s.outboundcontactid,
            s.outboundcontactlistid,
            s.dispositionname,
            s.userid,
            s.queueid,
            s.noutboundconnected,
            s.ttalkcomplete,
            s.theldcomplete,
            s.tacw,
            s.thandle,
            s.tacd,
            s.segmentstartdate AS event_ts,
            0 AS is_boundary
           FROM seg s
        UNION ALL
         SELECT s.conversationid,
            s.conversationstartdate,
            s.sessionid,
            s.purpose,
            s.mediatype,
            s.sessiondirection,
            s.segmenttype,
            s.segmentstartdate,
            s.segmentenddate,
            s.disconnectiontype,
            s.errorcode,
            s.sipresponsecodes,
            s.q850responsecodes,
            s.wrapupcode,
            s.callbackscheduledtime,
            s.outboundcampaignid,
            s.outboundcontactid,
            s.outboundcontactlistid,
            s.dispositionname,
            s.userid,
            s.queueid,
            s.noutboundconnected,
            s.ttalkcomplete,
            s.theldcomplete,
            s.tacw,
            s.thandle,
            s.tacd,
            s.segmentenddate AS event_ts,
            1 AS is_boundary
           FROM seg s
          WHERE (((s.purpose)::text = 'customer'::text) AND ((s.mediatype)::text = 'callback'::text) AND ((s.segmenttype)::text = 'scheduled'::text) AND (s.segmentenddate IS NOT NULL))
        ), grouped AS (
         SELECT e.conversationid,
            e.conversationstartdate,
            e.sessionid,
            e.purpose,
            e.mediatype,
            e.sessiondirection,
            e.segmenttype,
            e.segmentstartdate,
            e.segmentenddate,
            e.disconnectiontype,
            e.errorcode,
            e.sipresponsecodes,
            e.q850responsecodes,
            e.wrapupcode,
            e.callbackscheduledtime,
            e.outboundcampaignid,
            e.outboundcontactid,
            e.outboundcontactlistid,
            e.dispositionname,
            e.userid,
            e.queueid,
            e.noutboundconnected,
            e.ttalkcomplete,
            e.theldcomplete,
            e.tacw,
            e.thandle,
            e.tacd,
            e.event_ts,
            e.is_boundary,
            sum(e.is_boundary) OVER (PARTITION BY e.conversationid, e.conversationstartdate ORDER BY e.event_ts, e.is_boundary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS attempt_group
           FROM events e
        ), sess AS (
         SELECT grouped.conversationid,
            grouped.conversationstartdate,
            grouped.attempt_group,
            grouped.sessionid,
            max((grouped.purpose)::text) AS purpose,
            max((grouped.mediatype)::text) AS mediatype,
            max((grouped.sessiondirection)::text) AS sessiondirection,
            min(grouped.segmentstartdate) AS sess_start,
            min(
                CASE
                    WHEN ((grouped.segmenttype)::text <> 'scheduled'::text) THEN grouped.segmentstartdate
                    ELSE NULL::timestamp without time zone
                END) AS span_start,
            max(
                CASE
                    WHEN ((grouped.segmenttype)::text <> 'scheduled'::text) THEN grouped.segmentenddate
                    ELSE NULL::timestamp without time zone
                END) AS span_end,
            sum(
                CASE
                    WHEN (((grouped.segmenttype)::text <> 'scheduled'::text) AND (grouped.segmentenddate IS NULL)) THEN 1
                    ELSE 0
                END) AS open_segments,
            max(
                CASE
                    WHEN ((grouped.segmenttype)::text = 'interact'::text) THEN 1
                    ELSE 0
                END) AS answered,
            max(
                CASE
                    WHEN ((grouped.segmenttype)::text <> 'alert'::text) THEN 1
                    ELSE 0
                END) AS engaged,
            max(
                CASE
                    WHEN ((grouped.segmenttype)::text = 'scheduled'::text) THEN 1
                    ELSE 0
                END) AS scheduled,
            max(
                CASE
                    WHEN ((grouped.segmenttype)::text = 'scheduled'::text) THEN grouped.callbackscheduledtime
                    ELSE NULL::timestamp without time zone
                END) AS callbackscheduledtime,
            max((grouped.outboundcampaignid)::text) AS campaignid,
            max((grouped.outboundcontactid)::text) AS contactid,
            max((grouped.outboundcontactlistid)::text) AS contactlistid,
            max((grouped.queueid)::text) AS queueid,
            max((
                CASE
                    WHEN ((grouped.disconnectiontype)::text <> 'none'::text) THEN grouped.disconnectiontype
                    ELSE NULL::character varying
                END)::text) AS disconnectiontype,
            max((grouped.errorcode)::text) AS errorcode,
            max(grouped.sipresponsecodes) AS sipresponsecodes,
            max(grouped.q850responsecodes) AS q850responsecodes,
            max((grouped.dispositionname)::text) AS dispositionname,
            max(
                CASE
                    WHEN ((grouped.segmenttype)::text = 'wrapup'::text) THEN NULLIF((grouped.wrapupcode)::text, ''::text)
                    ELSE NULL::text
                END) AS wrapupcode,
            max(
                CASE
                    WHEN (((grouped.segmenttype)::text = 'wrapup'::text) AND (NULLIF((grouped.wrapupcode)::text, ''::text) IS NOT NULL)) THEN grouped.segmentstartdate
                    ELSE NULL::timestamp without time zone
                END) AS wrapup_time,
            max((grouped.userid)::text) AS userid,
            sum(COALESCE(grouped.noutboundconnected, 0)) AS noutboundconnected,
            sum(grouped.ttalkcomplete) AS ttalkcomplete,
            sum(grouped.theldcomplete) AS theldcomplete,
            sum(grouped.tacw) AS tacw,
            sum(grouped.thandle) AS thandle,
            sum(grouped.tacd) AS tacd,
            sum(
                CASE
                    WHEN ((grouped.segmenttype)::text = ANY ((ARRAY['contacting'::character varying, 'dialing'::character varying])::text[])) THEN (EXTRACT(epoch FROM (grouped.segmentenddate - grouped.segmentstartdate)) * (1000)::numeric)
                    ELSE NULL::numeric
                END) AS dial_ms
           FROM grouped
          WHERE (grouped.is_boundary = 0)
          GROUP BY grouped.conversationid, grouped.conversationstartdate, grouped.attempt_group, grouped.sessionid
        ), flagged AS (
         SELECT s.conversationid,
            s.conversationstartdate,
            s.attempt_group,
            s.sessionid,
            s.purpose,
            s.mediatype,
            s.sessiondirection,
            s.sess_start,
            s.span_start,
            s.span_end,
            s.open_segments,
            s.answered,
            s.engaged,
            s.scheduled,
            s.callbackscheduledtime,
            s.campaignid,
            s.contactid,
            s.contactlistid,
            s.queueid,
            s.disconnectiontype,
            s.errorcode,
            s.sipresponsecodes,
            s.q850responsecodes,
            s.dispositionname,
            s.wrapupcode,
            s.wrapup_time,
            s.userid,
            s.noutboundconnected,
            s.ttalkcomplete,
            s.theldcomplete,
            s.tacw,
            s.thandle,
            s.tacd,
            s.dial_ms,
                CASE
                    WHEN ((s.purpose = 'customer'::text) AND (s.mediatype <> 'callback'::text)) THEN 1
                    ELSE 0
                END AS is_dial,
                CASE
                    WHEN ((s.purpose = 'agent'::text) AND (s.engaged = 1)) THEN 1
                    ELSE 0
                END AS is_agent,
                CASE
                    WHEN ((s.purpose = 'agent'::text) AND (s.wrapupcode IS NOT NULL)) THEN 1
                    ELSE 0
                END AS has_wrapup
           FROM sess s
        ), ranked AS (
         SELECT f.conversationid,
            f.conversationstartdate,
            f.attempt_group,
            f.sessionid,
            f.purpose,
            f.mediatype,
            f.sessiondirection,
            f.sess_start,
            f.span_start,
            f.span_end,
            f.open_segments,
            f.answered,
            f.engaged,
            f.scheduled,
            f.callbackscheduledtime,
            f.campaignid,
            f.contactid,
            f.contactlistid,
            f.queueid,
            f.disconnectiontype,
            f.errorcode,
            f.sipresponsecodes,
            f.q850responsecodes,
            f.dispositionname,
            f.wrapupcode,
            f.wrapup_time,
            f.userid,
            f.noutboundconnected,
            f.ttalkcomplete,
            f.theldcomplete,
            f.tacw,
            f.thandle,
            f.tacd,
            f.dial_ms,
            f.is_dial,
            f.is_agent,
            f.has_wrapup,
            row_number() OVER (PARTITION BY f.conversationid, f.conversationstartdate, f.attempt_group, f.is_dial ORDER BY f.answered DESC, f.sess_start DESC, f.sessionid) AS dial_rank,
            row_number() OVER (PARTITION BY f.conversationid, f.conversationstartdate, f.attempt_group, f.is_agent ORDER BY f.sess_start, f.sessionid) AS agent_rank,
            row_number() OVER (PARTITION BY f.conversationid, f.conversationstartdate, f.attempt_group, f.has_wrapup ORDER BY
                CASE
                    WHEN (f.mediatype <> 'callback'::text) THEN 0
                    ELSE 1
                END, f.wrapup_time DESC, f.sessionid) AS wrap_rank
           FROM flagged f
        ), attempt AS (
         SELECT ranked.conversationid,
            ranked.conversationstartdate,
            ranked.attempt_group,
            min(ranked.span_start) AS attempt_start,
                CASE
                    WHEN (sum(ranked.open_segments) = (0)::numeric) THEN max(ranked.span_end)
                    ELSE NULL::timestamp without time zone
                END AS attempt_end,
            max(ranked.campaignid) AS campaignid,
            max(ranked.contactid) AS contactid,
            max(ranked.contactlistid) AS contactlistid,
            sum(ranked.is_dial) AS dial_count,
            max(
                CASE
                    WHEN (ranked.is_dial = 1) THEN ranked.answered
                    ELSE NULL::integer
                END) AS answered,
            sum(
                CASE
                    WHEN (ranked.is_dial = 1) THEN ranked.dial_ms
                    ELSE NULL::numeric
                END) AS dial_ms,
            max(
                CASE
                    WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.mediatype
                    ELSE NULL::text
                END) AS mediatype,
            max(
                CASE
                    WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.sessiondirection
                    ELSE NULL::text
                END) AS sessiondirection,
            COALESCE(max(
                CASE
                    WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.queueid
                    ELSE NULL::text
                END), max(ranked.queueid)) AS queueid,
            max(
                CASE
                    WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.disconnectiontype
                    ELSE NULL::text
                END) AS disconnectiontype,
            max(
                CASE
                    WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.errorcode
                    ELSE NULL::text
                END) AS errorcode,
            max(
                CASE
                    WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.sipresponsecodes
                    ELSE NULL::text
                END) AS sipresponsecodes,
            max(
                CASE
                    WHEN ((ranked.is_dial = 1) AND (ranked.dial_rank = 1)) THEN ranked.q850responsecodes
                    ELSE NULL::text
                END) AS q850responsecodes,
            max(
                CASE
                    WHEN (ranked.purpose = 'outbound'::text) THEN ranked.dispositionname
                    ELSE NULL::text
                END) AS dispositionname,
            max(
                CASE
                    WHEN ((ranked.purpose = 'customer'::text) AND (ranked.mediatype = 'callback'::text)) THEN ranked.scheduled
                    ELSE 0
                END) AS scheduled_callback,
            max(
                CASE
                    WHEN ((ranked.purpose = 'customer'::text) AND (ranked.mediatype = 'callback'::text)) THEN ranked.callbackscheduledtime
                    ELSE NULL::timestamp without time zone
                END) AS callbackscheduledtime,
            sum(ranked.noutboundconnected) AS connected,
            sum(
                CASE
                    WHEN ((ranked.purpose = 'agent'::text) AND (ranked.mediatype <> 'callback'::text)) THEN ranked.ttalkcomplete
                    ELSE NULL::numeric
                END) AS ttalkcomplete,
            sum(
                CASE
                    WHEN ((ranked.purpose = 'agent'::text) AND (ranked.mediatype <> 'callback'::text)) THEN ranked.theldcomplete
                    ELSE NULL::numeric
                END) AS theldcomplete,
            sum(
                CASE
                    WHEN (ranked.purpose = 'agent'::text) THEN ranked.tacw
                    ELSE NULL::numeric
                END) AS tacw,
            sum(
                CASE
                    WHEN (ranked.purpose = 'agent'::text) THEN ranked.thandle
                    ELSE NULL::numeric
                END) AS thandle,
            sum(
                CASE
                    WHEN (ranked.purpose = 'acd'::text) THEN ranked.tacd
                    ELSE NULL::numeric
                END) AS tacd,
            max(
                CASE
                    WHEN ((ranked.is_agent = 1) AND (ranked.agent_rank = 1)) THEN ranked.userid
                    ELSE NULL::text
                END) AS userid,
            max(
                CASE
                    WHEN ((ranked.has_wrapup = 1) AND (ranked.wrap_rank = 1)) THEN ranked.wrapupcode
                    ELSE NULL::text
                END) AS wrapupcode
           FROM ranked
          GROUP BY ranked.conversationid, ranked.conversationstartdate, ranked.attempt_group
         HAVING (sum(ranked.is_dial) > 0)
        ), classified AS (
         SELECT a.conversationid,
            a.conversationstartdate,
            a.attempt_group,
            a.attempt_start,
            a.attempt_end,
            a.campaignid,
            a.contactid,
            a.contactlistid,
            a.dial_count,
            a.answered,
            a.dial_ms,
            a.mediatype,
            a.sessiondirection,
            a.queueid,
            a.disconnectiontype,
            a.errorcode,
            a.sipresponsecodes,
            a.q850responsecodes,
            a.dispositionname,
            a.scheduled_callback,
            a.callbackscheduledtime,
            a.connected,
            a.ttalkcomplete,
            a.theldcomplete,
            a.tacw,
            a.thandle,
            a.tacd,
            a.userid,
            a.wrapupcode,
            dense_rank() OVER (PARTITION BY a.conversationid, a.conversationstartdate ORDER BY a.attempt_group) AS attempt_seq,
            ((a.connected > (0)::numeric) OR ((a.dispositionname IS NULL) AND (a.answered = 1))) AS reached,
            ((','::text || replace(replace(replace(COALESCE(a.sipresponsecodes, ''::text), '['::text, ''::text), ']'::text, ''::text), ' '::text, ''::text)) || ','::text) AS sip_list,
            ((','::text || replace(replace(replace(COALESCE(a.q850responsecodes, ''::text), '['::text, ''::text), ']'::text, ''::text), ' '::text, ''::text)) || ','::text) AS q850_list,
                CASE
                    WHEN (a.dispositionname IS NULL) THEN NULL::integer
                    WHEN (a.dispositionname ~~ 'disposition.classification.uncallable.%'::text) THEN 1
                    WHEN (a.dispositionname = 'disposition.classification.callable.sit'::text) THEN 1
                    WHEN ((a.dispositionname = 'disposition.classification.callable.disconnect'::text) AND (a.answered = 0)) THEN 1
                    ELSE 0
                END AS disposition_failed
           FROM attempt a
        ), resolved AS (
         SELECT c_1.conversationid,
            c_1.conversationstartdate,
            c_1.attempt_group,
            c_1.attempt_start,
            c_1.attempt_end,
            c_1.campaignid,
            c_1.contactid,
            c_1.contactlistid,
            c_1.dial_count,
            c_1.answered,
            c_1.dial_ms,
            c_1.mediatype,
            c_1.sessiondirection,
            c_1.queueid,
            c_1.disconnectiontype,
            c_1.errorcode,
            c_1.sipresponsecodes,
            c_1.q850responsecodes,
            c_1.dispositionname,
            c_1.scheduled_callback,
            c_1.callbackscheduledtime,
            c_1.connected,
            c_1.ttalkcomplete,
            c_1.theldcomplete,
            c_1.tacw,
            c_1.thandle,
            c_1.tacd,
            c_1.userid,
            c_1.wrapupcode,
            c_1.attempt_seq,
            c_1.reached,
            c_1.sip_list,
            c_1.q850_list,
            c_1.disposition_failed,
                CASE
                    WHEN (c_1.dispositionname IS NOT NULL) THEN NULL::text
                    WHEN c_1.reached THEN 'answered'::text
                    WHEN ((c_1.sip_list ~~ '%,486,%'::text) OR (c_1.sip_list ~~ '%,600,%'::text)) THEN 'busy'::text
                    WHEN ((c_1.sip_list ~~ '%,408,%'::text) OR (c_1.sip_list ~~ '%,480,%'::text) OR (c_1.sip_list ~~ '%,487,%'::text)) THEN 'no_answer'::text
                    WHEN (c_1.sip_list <> ',,'::text) THEN 'failed'::text
                    WHEN (c_1.q850_list ~~ '%,17,%'::text) THEN 'busy'::text
                    WHEN ((c_1.q850_list ~~ '%,16,%'::text) OR (c_1.q850_list ~~ '%,18,%'::text) OR (c_1.q850_list ~~ '%,19,%'::text) OR (c_1.q850_list ~~ '%,31,%'::text) OR (c_1.q850_list ~~ '%,102,%'::text)) THEN 'no_answer'::text
                    WHEN (c_1.q850_list <> ',,'::text) THEN 'failed'::text
                    WHEN ((c_1.errorcode IS NOT NULL) OR (c_1.disconnectiontype = 'error'::text)) THEN 'failed'::text
                    WHEN (c_1.disconnectiontype = 'peer'::text) THEN 'no_answer'::text
                    ELSE NULL::text
                END AS dial_result
           FROM classified c_1
        )
 SELECT (((r.conversationid)::text || '|'::text) || r.attempt_seq) AS attempt_id,
    r.conversationid AS conversation_id,
    r.contactid AS contact_id,
    NULL::character varying(255) AS contact_name,
    r.campaignid AS campaign_id,
    COALESCE(c.name, mc.name) AS campaign_name,
    r.contactlistid AS contact_list_id,
    cl.name AS contact_list_name,
    (r.attempt_start)::timestamp(3) without time zone AS attempt_start_ts,
    (r.attempt_end)::timestamp(3) without time zone AS attempt_end_ts,
    (r.attempt_start)::date AS attempt_date,
    r.userid AS agent_id,
    ud.name AS agent_name,
    ud.managerid AS manager_id,
    ud.managername AS manager_name,
    tm.teamid AS team_id,
    td.name AS team_name,
    r.queueid AS queue_id,
    qd.name AS queue_name,
    r.mediatype AS media_type,
    r.sessiondirection AS direction,
    r.wrapupcode AS wrap_up_code,
    wd.name AS wrap_up_name,
        CASE
            WHEN (r.reached AND (r.wrapupcode IS NOT NULL) AND wd.isrightpartycontact) THEN 'Right Party Contact'::text
            WHEN r.reached THEN 'Connected'::text
            ELSE 'Not Connected'::text
        END AS connect_status,
    r.reached AS contact_reached,
        CASE
            WHEN (r.wrapupcode IS NOT NULL) THEN wd.isrightpartycontact
            WHEN (NOT r.reached) THEN false
            ELSE NULL::boolean
        END AS right_party_contact,
        CASE
            WHEN (r.dispositionname IS NOT NULL) THEN (r.dispositionname ~~ '%.callable.machine'::text)
            ELSE NULL::boolean
        END AS answering_machine,
        CASE
            WHEN (r.dispositionname IS NOT NULL) THEN (r.dispositionname ~~ '%.callable.noanswer'::text)
            WHEN (r.dial_result IS NOT NULL) THEN (r.dial_result = 'no_answer'::text)
            ELSE NULL::boolean
        END AS no_answer,
        CASE
            WHEN (r.dispositionname IS NOT NULL) THEN (r.dispositionname ~~ '%.callable.busy'::text)
            WHEN (r.dial_result IS NOT NULL) THEN (r.dial_result = 'busy'::text)
            ELSE NULL::boolean
        END AS busy,
        CASE
            WHEN (r.dispositionname IS NOT NULL) THEN (r.disposition_failed = 1)
            WHEN (r.dial_result IS NOT NULL) THEN (r.dial_result = 'failed'::text)
            ELSE NULL::boolean
        END AS failed,
    (r.scheduled_callback = 1) AS callback_required,
    (r.callbackscheduledtime)::timestamp(3) without time zone AS callback_scheduled_ts,
    r.disconnectiontype AS disconnect_type,
    NULLIF(TRIM(BOTH FROM ((COALESCE((r.errorcode || ' '::text), ''::text) || COALESCE((('SIP '::text || r.sipresponsecodes) || ' '::text), ''::text)) || COALESCE(('Q.850 '::text || r.q850responsecodes), ''::text))), ''::text) AS disconnect_reason,
    r.dial_count,
    (round(r.tacd))::integer AS queue_time_seconds,
    (round((r.dial_ms / 1000.0)))::integer AS dial_time_seconds,
    (round(EXTRACT(epoch FROM (r.attempt_end - r.attempt_start))))::integer AS duration_seconds,
    (round(r.ttalkcomplete))::integer AS talk_time_seconds,
    (round(r.theldcomplete))::integer AS hold_time_seconds,
    (round(r.tacw))::integer AS wrap_up_duration_seconds,
    (round(r.thandle))::integer AS handle_time_seconds,
    'GenesysCloud'::character varying(50) AS source_system,
    ((now() AT TIME ZONE 'utc'::text))::timestamp(3) without time zone AS load_ts,
    r.conversationstartdate
   FROM ((((((((resolved r
     LEFT JOIN vwuserdetail ud ON (((ud.id)::text = r.userid)))
     LEFT JOIN ( SELECT teammemberdata.userid,
            min((teammemberdata.teamid)::text) AS teamid
           FROM teammemberdata
          GROUP BY teammemberdata.userid) tm ON (((tm.userid)::text = r.userid)))
     LEFT JOIN teamdetails td ON (((td.id)::text = tm.teamid)))
     LEFT JOIN odcampaigndetails c ON (((c.id)::text = r.campaignid)))
     LEFT JOIN odmessagingcampaigndetails mc ON (((mc.id)::text = r.campaignid)))
     LEFT JOIN odcontactlistdetails cl ON (((cl.id)::text = r.contactlistid)))
     LEFT JOIN queuedetails qd ON (((qd.id)::text = r.queueid)))
     LEFT JOIN wrapupdetails wd ON (((wd.id)::text = r.wrapupcode)))
)

Columns

Name Type Default Nullable Children Parents Comment
attempt_id text true
conversation_id varchar(50) true
contact_id text true
contact_name varchar(255) true
campaign_id text true
campaign_name varchar(200) true
contact_list_id text true
contact_list_name varchar(200) true
attempt_start_ts timestamp(3) without time zone true
attempt_end_ts timestamp(3) without time zone true
attempt_date date true
agent_id text true
agent_name varchar(200) true
manager_id varchar(50) true
manager_name varchar(200) true
team_id text true
team_name varchar(200) true
queue_id text true
queue_name varchar(255) true
media_type text true
direction text true
wrap_up_code text true
wrap_up_name varchar(255) true
connect_status text true
contact_reached boolean true
right_party_contact boolean true
answering_machine boolean true
no_answer boolean true
busy boolean true
failed boolean true
callback_required boolean true
callback_scheduled_ts timestamp(3) without time zone true
disconnect_type text true
disconnect_reason text true
dial_count bigint true
queue_time_seconds integer true
dial_time_seconds integer true
duration_seconds integer true
talk_time_seconds integer true
hold_time_seconds integer true
wrap_up_duration_seconds integer true
handle_time_seconds integer true
source_system varchar(50) true
load_ts timestamp(3) without time zone true
conversationstartdate timestamp without time zone true

Referenced Tables

Name Columns Comment Type
public.detailedinteractiondata 154 Conversation Detailed Data BASE TABLE
events 0
grouped.segmentenddate 0
grouped 0
sess 0
flagged 0
ranked 0
attempt 0
classified 0
COALESCE 0
r.attempt_end 0
resolved 0
public.vwuserdetail 18 User details with manager and division context VIEW
public.teammemberdata 4 Work Team Membership Data BASE TABLE
public.teamdetails 7 BASE TABLE
public.odcampaigndetails 33 Outbound Dialling Campaign Lookup Data BASE TABLE
public.odmessagingcampaigndetails 26 Outbound Messaging Campaign Lookup Data (SMS, Email, WhatsApp) BASE TABLE
public.odcontactlistdetails 10 BASE TABLE
public.queuedetails 25 Queue Lookup data BASE TABLE
public.wrapupdetails 7 Wrap Up Code Details Lookup Up Data BASE TABLE