CREATE VIEW vwoutboundattemptdatabuild AS (
WITH seg AS (
SELECT di.conversationid,
di.conversationstartdate,
di.sessionid,
di.peer,
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.noutboundconnected,
di.outboundcampaignid,
di.outboundcontactid,
di.outboundcontactlistid,
di.dispositionname,
di.dispositionanalyzer,
di.userid,
di.queueid,
di.ttalkcomplete,
di.theldcomplete,
di.tacw
FROM detailedinteractiondata di
WHERE ((di.outboundcampaignid IS NOT NULL) AND (di.conversationid IS NOT NULL) AND (di.sessionid IS NOT NULL))
), sess AS (
SELECT seg.conversationid,
seg.conversationstartdate,
seg.sessionid,
max((seg.peer)::text) AS peer,
max((seg.purpose)::text) AS purpose,
max((seg.mediatype)::text) AS mediatype,
max((seg.sessiondirection)::text) AS sessiondirection,
min(seg.segmentstartdate) AS sess_start,
CASE
WHEN (count(*) = count(seg.segmentenddate)) THEN max(seg.segmentenddate)
ELSE NULL::timestamp without time zone
END AS sess_end,
max(
CASE
WHEN ((seg.segmenttype)::text = 'interact'::text) THEN 1
ELSE 0
END) AS answered,
max(
CASE
WHEN ((seg.segmenttype)::text <> 'alert'::text) THEN 1
ELSE 0
END) AS engaged,
max((seg.outboundcampaignid)::text) AS campaignid,
max((seg.outboundcontactid)::text) AS contactid,
max((seg.outboundcontactlistid)::text) AS contactlistid,
max((
CASE
WHEN ((seg.disconnectiontype)::text <> 'none'::text) THEN seg.disconnectiontype
ELSE NULL::character varying
END)::text) AS disconnectiontype,
max((seg.errorcode)::text) AS errorcode,
max(seg.sipresponsecodes) AS sipresponsecodes,
max(seg.q850responsecodes) AS q850responsecodes,
max((seg.dispositionname)::text) AS dispositionname,
max((seg.dispositionanalyzer)::text) AS dispositionanalyzer,
max(
CASE
WHEN ((seg.segmenttype)::text = 'wrapup'::text) THEN NULLIF((seg.wrapupcode)::text, ''::text)
ELSE NULL::text
END) AS wrapupcode,
max(seg.callbackscheduledtime) AS callbackscheduledtime,
max((seg.userid)::text) AS userid,
max((seg.queueid)::text) AS queueid,
sum(COALESCE(seg.noutboundconnected, 0)) AS noutboundconnected,
sum(seg.ttalkcomplete) AS ttalkcomplete,
sum(seg.theldcomplete) AS theldcomplete,
sum(seg.tacw) AS tacw
FROM seg
GROUP BY seg.conversationid, seg.conversationstartdate, seg.sessionid
), placed AS (
SELECT s.conversationid,
s.conversationstartdate,
s.sessionid,
s.peer,
s.purpose,
s.mediatype,
s.sessiondirection,
s.sess_start,
s.sess_end,
s.answered,
s.engaged,
s.campaignid,
s.contactid,
s.contactlistid,
s.disconnectiontype,
s.errorcode,
s.sipresponsecodes,
s.q850responsecodes,
s.dispositionname,
s.dispositionanalyzer,
s.wrapupcode,
s.callbackscheduledtime,
s.userid,
s.queueid,
s.noutboundconnected,
s.ttalkcomplete,
s.theldcomplete,
s.tacw,
CASE
WHEN ((s.purpose = 'customer'::text) AND (s.mediatype <> 'callback'::text)) THEN 1
ELSE 0
END AS is_attempt,
CASE
WHEN ((s.purpose = 'agent'::text) AND (s.mediatype <> 'callback'::text) AND (s.engaged = 1)) THEN 1
ELSE 0
END AS is_agent,
CASE
WHEN ((s.purpose = 'customer'::text) AND (s.mediatype <> 'callback'::text)) THEN s.sessionid
WHEN ((s.purpose = ANY (ARRAY['outbound'::text, 'acd'::text, 'agent'::text])) AND (s.mediatype <> 'callback'::text)) THEN (s.peer)::character varying
ELSE NULL::character varying
END AS attempt_sessionid,
max(
CASE
WHEN ((s.purpose = 'customer'::text) AND (s.mediatype <> 'callback'::text)) THEN s.sess_start
ELSE NULL::timestamp without time zone
END) OVER (PARTITION BY s.conversationid, s.conversationstartdate ORDER BY s.sess_start, s.sessionid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS latest_attempt_start
FROM sess s
), ranked AS (
SELECT p.conversationid,
p.conversationstartdate,
p.sessionid,
p.peer,
p.purpose,
p.mediatype,
p.sessiondirection,
p.sess_start,
p.sess_end,
p.answered,
p.engaged,
p.campaignid,
p.contactid,
p.contactlistid,
p.disconnectiontype,
p.errorcode,
p.sipresponsecodes,
p.q850responsecodes,
p.dispositionname,
p.dispositionanalyzer,
p.wrapupcode,
p.callbackscheduledtime,
p.userid,
p.queueid,
p.noutboundconnected,
p.ttalkcomplete,
p.theldcomplete,
p.tacw,
p.is_attempt,
p.is_agent,
p.attempt_sessionid,
p.latest_attempt_start,
row_number() OVER (PARTITION BY p.conversationid, p.conversationstartdate, p.attempt_sessionid, p.is_agent ORDER BY p.sess_start, p.sessionid) AS agent_rank,
max(
CASE
WHEN ((p.purpose = 'customer'::text) AND (p.mediatype = 'callback'::text)) THEN 1
ELSE 0
END) OVER (PARTITION BY p.conversationid, p.conversationstartdate, p.latest_attempt_start) AS cb_hascallback,
max(
CASE
WHEN ((p.purpose = 'customer'::text) AND (p.mediatype = 'callback'::text)) THEN p.callbackscheduledtime
ELSE NULL::timestamp without time zone
END) OVER (PARTITION BY p.conversationid, p.conversationstartdate, p.latest_attempt_start) AS cb_scheduledtime
FROM placed p
), attempt AS (
SELECT ranked.conversationid,
ranked.conversationstartdate,
ranked.attempt_sessionid AS sessionid,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.sess_start
ELSE NULL::timestamp without time zone
END) AS attempt_start,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.sess_end
ELSE NULL::timestamp without time zone
END) AS attempt_end,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.answered
ELSE NULL::integer
END) AS answered,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.mediatype
ELSE NULL::text
END) AS mediatype,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.sessiondirection
ELSE NULL::text
END) AS sessiondirection,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.campaignid
ELSE NULL::text
END) AS campaignid,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.contactid
ELSE NULL::text
END) AS contactid,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.contactlistid
ELSE NULL::text
END) AS contactlistid,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.queueid
ELSE NULL::text
END) AS queueid,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.disconnectiontype
ELSE NULL::text
END) AS disconnectiontype,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.errorcode
ELSE NULL::text
END) AS errorcode,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.sipresponsecodes
ELSE NULL::text
END) AS sipresponsecodes,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.q850responsecodes
ELSE NULL::text
END) AS q850responsecodes,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.cb_hascallback
ELSE NULL::integer
END) AS hascallback,
max(
CASE
WHEN (ranked.is_attempt = 1) THEN ranked.cb_scheduledtime
ELSE NULL::timestamp without time zone
END) AS callbackscheduledtime,
max(
CASE
WHEN (ranked.purpose = 'outbound'::text) THEN ranked.dispositionname
ELSE NULL::text
END) AS dispositionname,
max(
CASE
WHEN (ranked.purpose = 'outbound'::text) THEN ranked.dispositionanalyzer
ELSE NULL::text
END) AS dispositionanalyzer,
max(
CASE
WHEN (ranked.purpose = 'outbound'::text) THEN ranked.wrapupcode
ELSE NULL::text
END) AS systemcode,
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.is_agent = 1) AND (ranked.agent_rank = 1)) THEN ranked.wrapupcode
ELSE NULL::text
END) AS wrapupcode,
max(
CASE
WHEN ((ranked.is_agent = 1) AND (ranked.agent_rank = 1)) THEN ranked.ttalkcomplete
ELSE NULL::numeric
END) AS ttalkcomplete,
max(
CASE
WHEN ((ranked.is_agent = 1) AND (ranked.agent_rank = 1)) THEN ranked.theldcomplete
ELSE NULL::numeric
END) AS theldcomplete,
max(
CASE
WHEN ((ranked.is_agent = 1) AND (ranked.agent_rank = 1)) THEN ranked.tacw
ELSE NULL::numeric
END) AS tacw,
sum(
CASE
WHEN (ranked.is_agent = 1) THEN ranked.noutboundconnected
ELSE (0)::bigint
END) AS connected
FROM ranked
WHERE (ranked.attempt_sessionid IS NOT NULL)
GROUP BY ranked.conversationid, ranked.conversationstartdate, ranked.attempt_sessionid
HAVING (max(ranked.is_attempt) = 1)
), classified AS (
SELECT a_1.conversationid,
a_1.conversationstartdate,
a_1.sessionid,
a_1.attempt_start,
a_1.attempt_end,
a_1.answered,
a_1.mediatype,
a_1.sessiondirection,
a_1.campaignid,
a_1.contactid,
a_1.contactlistid,
a_1.queueid,
a_1.disconnectiontype,
a_1.errorcode,
a_1.sipresponsecodes,
a_1.q850responsecodes,
a_1.hascallback,
a_1.callbackscheduledtime,
a_1.dispositionname,
a_1.dispositionanalyzer,
a_1.systemcode,
a_1.userid,
a_1.wrapupcode,
a_1.ttalkcomplete,
a_1.theldcomplete,
a_1.tacw,
a_1.connected,
CASE
WHEN (a_1.dispositionname IS NULL) THEN NULL::integer
WHEN (a_1.dispositionname ~~ 'disposition.classification.uncallable.%'::text) THEN 1
WHEN (a_1.dispositionname = 'disposition.classification.callable.sit'::text) THEN 1
WHEN ((a_1.dispositionname = 'disposition.classification.callable.disconnect'::text) AND (a_1.answered = 0)) THEN 1
ELSE 0
END AS failed
FROM attempt a_1
)
SELECT (((a.conversationid)::text || '|'::text) || (a.sessionid)::text) AS attempt_id,
a.conversationid AS conversation_id,
a.contactid AS contact_id,
a.campaignid AS campaign_id,
COALESCE(c.name, mc.name) AS campaign_name,
a.contactlistid AS contact_list_id,
cl.name AS contact_list_name,
(a.attempt_start)::timestamp(3) without time zone AS attempt_start_ts,
(a.attempt_end)::timestamp(3) without time zone AS attempt_end_ts,
(a.attempt_start)::date AS attempt_date,
a.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,
a.queueid AS queue_id,
qd.name AS queue_name,
a.mediatype AS media_type,
a.sessiondirection AS direction,
a.systemcode AS outcome_code,
a.dispositionname AS outcome_description,
a.wrapupcode AS wrap_up_code,
wd.name AS wrap_up_name,
CASE
WHEN (a.connected > (0)::numeric) THEN 'Connected'::text
WHEN (a.failed = 1) THEN 'Failed'::text
WHEN (a.dispositionname IS NOT NULL) THEN 'Not Connected'::text
ELSE NULL::text
END AS connect_status,
(a.connected > (0)::numeric) AS contact_reached,
CASE
WHEN (a.wrapupcode IS NOT NULL) THEN wd.isrightpartycontact
WHEN ((a.failed = 1) OR (a.dispositionname ~~ '%.callable.machine'::text) OR (a.dispositionname ~~ '%.callable.noanswer'::text) OR (a.dispositionname ~~ '%.callable.busy'::text)) THEN false
ELSE NULL::boolean
END AS right_party_contact,
CASE
WHEN (a.dispositionname IS NULL) THEN NULL::boolean
ELSE (a.dispositionname ~~ '%.callable.machine'::text)
END AS answering_machine,
CASE
WHEN (a.dispositionname IS NULL) THEN NULL::boolean
ELSE (a.dispositionname ~~ '%.callable.noanswer'::text)
END AS no_answer,
CASE
WHEN (a.dispositionname IS NULL) THEN NULL::boolean
ELSE (a.dispositionname ~~ '%.callable.busy'::text)
END AS busy,
CASE
WHEN (a.failed IS NULL) THEN NULL::boolean
ELSE (a.failed = 1)
END AS failed,
(a.hascallback = 1) AS callback_required,
(a.callbackscheduledtime)::timestamp(3) without time zone AS callback_scheduled_ts,
a.disconnectiontype AS disconnect_type,
COALESCE(a.errorcode, ('SIP '::text || a.sipresponsecodes), ('Q.850 '::text || a.q850responsecodes)) AS disconnect_reason,
(round(EXTRACT(epoch FROM (a.attempt_end - a.attempt_start))))::integer AS duration_seconds,
(round(a.ttalkcomplete))::integer AS talk_time_seconds,
(round(a.theldcomplete))::integer AS hold_time_seconds,
(round(a.tacw))::integer AS wrap_up_duration_seconds,
'GenesysCloud'::character varying(50) AS source_system,
((now() AT TIME ZONE 'utc'::text))::timestamp(3) without time zone AS load_ts,
a.conversationstartdate
FROM ((((((((classified a
LEFT JOIN vwuserdetail ud ON (((ud.id)::text = a.userid)))
LEFT JOIN ( SELECT teammemberdata.userid,
min((teammemberdata.teamid)::text) AS teamid
FROM teammemberdata
GROUP BY teammemberdata.userid) tm ON (((tm.userid)::text = a.userid)))
LEFT JOIN teamdetails td ON (((td.id)::text = tm.teamid)))
LEFT JOIN odcampaigndetails c ON (((c.id)::text = a.campaignid)))
LEFT JOIN odmessagingcampaigndetails mc ON (((mc.id)::text = a.campaignid)))
LEFT JOIN odcontactlistdetails cl ON (((cl.id)::text = a.contactlistid)))
LEFT JOIN queuedetails qd ON (((qd.id)::text = a.queueid)))
LEFT JOIN wrapupdetails wd ON (((wd.id)::text = a.wrapupcode)))
)