Flow Outcome Data with conversation summary enrichment and outcome name
CREATE VIEW vwflowoutcomedata AS (
SELECT fod.keyid,
fod.conversationid,
fod.conversationstartdate,
fod.conversationstartdateltc,
fod.conversationenddate,
fod.conversationenddateltc,
fod.flowoutcomestartdate,
fod.flowoutcomestartdateltc,
fod.flowoutcomeenddate,
fod.flowoutcomeenddateltc,
fod.flowid,
fod.flowname,
fod.flowversion,
fod.flowtype,
fod.flowoutcomeid,
fod.flowoutcome,
fod.flowoutcomevalue,
fodetl.name AS flowoutcomename,
cs.firstqueueid,
cs.firstqueuename,
cs.lastqueueid,
cs.lastqueuename,
cs.firstagentid,
cs.firstagentname,
cs.lastagentid,
cs.lastagentname,
cs.divisionid,
dd.name AS divisionname,
fod.updated
FROM (((flowoutcomedata fod
LEFT JOIN flowoutcomedetails fodetl ON (((fodetl.id)::text = (fod.flowoutcomeid)::text)))
LEFT JOIN vwconvsummarydata cs ON (((cs.conversationid)::text = (fod.conversationid)::text)))
LEFT JOIN divisiondetails dd ON (((cs.divisionid)::text = (dd.id)::text)))
)
Name | Type | Default | Nullable | Children | Parents | Comment |
---|---|---|---|---|---|---|
keyid | varchar(255) | true | Primary Key | |||
conversationid | varchar(50) | true | Conversation GUID | |||
conversationstartdate | timestamp without time zone | true | Conversation Start Date (UTC) | |||
conversationstartdateltc | timestamp without time zone | true | Conversation Start Date (LTC) | |||
conversationenddate | timestamp without time zone | true | Conversation End Date (UTC) | |||
conversationenddateltc | timestamp without time zone | true | Conversation End Date (LTC) | |||
flowoutcomestartdate | timestamp without time zone | true | Flow Outcome Start (UTC) | |||
flowoutcomestartdateltc | timestamp without time zone | true | Flow Outcome Start (LTC) | |||
flowoutcomeenddate | timestamp without time zone | true | Flow Outcome End (UTC) | |||
flowoutcomeenddateltc | timestamp without time zone | true | Flow Outcome End (LTC) | |||
flowid | varchar(50) | true | Flow GUID | |||
flowname | varchar(255) | true | Flow Name | |||
flowversion | numeric(20,2) | true | Flow Version | |||
flowtype | varchar(50) | true | Flow Type | |||
flowoutcomeid | varchar(50) | true | Flow Outcome GUID | |||
flowoutcome | varchar(50) | true | Flow Outcome GUID Combined | |||
flowoutcomevalue | varchar(50) | true | Flow Outcome Value | |||
flowoutcomename | varchar(50) | true | Flow Outcome Name (from details) | |||
firstqueueid | varchar(50) | true | First Queue GUID | |||
firstqueuename | varchar(255) | true | First Queue Name | |||
lastqueueid | varchar(50) | true | Last Queue GUID | |||
lastqueuename | varchar(255) | true | Last Queue Name | |||
firstagentid | varchar(50) | true | First Agent GUID | |||
firstagentname | varchar(200) | true | First Agent Name | |||
lastagentid | varchar(50) | true | Last Agent GUID | |||
lastagentname | varchar(200) | true | Last Agent Name | |||
divisionid | varchar(50) | true | Division GUID | |||
divisionname | varchar(50) | true | Division Name | |||
updated | timestamp without time zone | true | Row Updated Date (UTC) |
Name | Columns | Comment | Type |
---|---|---|---|
public.flowoutcomedata | 18 | Conversation Flow Outcome Data | BASE TABLE |
public.flowoutcomedetails | 13 | Flow Outcome Details | BASE TABLE |
public.vwconvsummarydata | 68 | See ConvSummaryData - Expands all the GUIDs with their lookups | VIEW |
public.divisiondetails | 4 | BASE TABLE |