public.vwcalldetail

public.vwcalldetail

Description

See DetailedInteractionData - Expands all the GUIDs with their lookups

Table Definition

CREATE VIEW vwcalldetail AS (
 SELECT di.conversationid,
    di.originaldirection,
    di.mediatype,
    di.ani,
    di.dnis,
    di.purpose,
    di.sessiondirection,
    di.conversationstartdate,
    di.userid,
    di.queueid,
    qd.name AS queuename,
    di.conversationenddate,
    datediff('second'::character varying, di.conversationstartdate, di.conversationenddate) AS callduration,
    sum(datediff('second'::character varying, di.segmentstartdate, di.segmentenddate)) AS talkduration,
    ((datediff('second'::character varying, di.conversationstartdate, di.conversationenddate))::numeric / 86400.00) AS calldurationday,
    ((sum(datediff('second'::character varying, di.segmentstartdate, di.segmentenddate)))::numeric / 86400.00) AS talkdurationday,
    ud.name,
    di.segmenttype
   FROM ((detailedinteractiondata di
     LEFT JOIN userdetails ud ON (((ud.id)::text = (di.userid)::text)))
     LEFT JOIN queuedetails qd ON (((qd.id)::text = (di.queueid)::text)))
  WHERE ((NOT (di.userid IS NULL)) AND ((di.purpose)::text = ANY ((ARRAY['agent'::character varying, 'user'::character varying, 'voicemail'::character varying])::text[])) AND ((di.segmenttype)::text = ANY ((ARRAY['Interact'::character varying, 'contacting'::character varying, 'dialing'::character varying])::text[])))
  GROUP BY di.conversationid, di.originaldirection, di.mediatype, di.ani, di.dnis, di.purpose, di.sessiondirection, di.conversationstartdate, di.userid, di.queueid, qd.name, di.conversationenddate, ud.name, di.segmenttype
)

Columns

Name Type Default Nullable Children Parents Comment
conversationid varchar(50) true Conversation GUID
originaldirection varchar(50) true Original Direction
mediatype varchar(50) true Media Type
ani varchar(400) true ANI
dnis varchar(400) true DNIS
purpose varchar(50) true Purpose
sessiondirection varchar(50) true Session Direction
conversationstartdate timestamp without time zone true Conversation Start Date
userid varchar(50) true User GUID
queueid varchar(50) true Queue GUID
queuename varchar(255) true Queue Name
conversationenddate timestamp without time zone true Conversation End Date
callduration integer true Call Duration
talkduration bigint true Talk Duration
calldurationday numeric true Call Duration per Day
talkdurationday numeric true Talk Duration per Day
name varchar(200) true Name
segmenttype varchar(50) true Segment Type

Referenced Tables

Name Columns Comment Type
public.detailedinteractiondata 138 Conversation Detailed Data BASE TABLE
public.userdetails 13 BASE TABLE
public.queuedetails 17 Queue Lookup data BASE TABLE

    • Related Articles

    • public.mvwconvvoiceoverviewdata

      Description Columns Name Type Default Nullable Children Parents Comment conversationid varchar(50) false sentimentscore numeric(20,2) true sentimenttrend numeric(20,2) true agentdurationpercentage numeric(20,2) true customerdurationpercentage ...
    • public.mvwconvvoicesentimentdetaildata

      Description Columns Name Type Default Nullable Children Parents Comment keyid varchar(100) false conversationid varchar(50) true starttime timestamp without time zone true starttimeltc timestamp without time zone true duration numeric(20,2) true ...
    • public.vwteammemberdata

      Description Team Member Data View Table Definition CREATE VIEW vwteammemberdata AS ( SELECT tm.userid, ud.name AS "Agent Name", td.name AS "Team Name" FROM ((teammemberdata tm LEFT JOIN vwuserdetail ud ON (((tm.userid)::text = (ud.id)::text))) LEFT ...
    • public.jobminimumdefinition

      Description Columns Name Type Default Nullable Children Parents Comment jobid integer nextval(‘jobminimumdefinition_jobid_seq’::regclass) false jobname varchar(255) true maxsyncspan varchar(50) true lookbackspan varchar(50) true Constraints Name Type ...
    • public.participantattributesdynamic

      Description Columns Name Type Default Nullable Children Parents Comment keyid varchar(50) false conversationid varchar(50) false conversationstartdate timestamp without time zone false conversationstartdateltc timestamp without time zone true ...