public.mvwevaluationoverview

public.mvwevaluationoverview

Description

Materialized view for evaluation overview data

Table Definition

CREATE MATERIALIZED VIEW mvwevaluationoverview AS (
 SELECT concat(evaldata.conversationid, '|', evaldata.evaluationid, '|', evaldata.evaluationformid) AS keyid,
    evaldata.conversationid,
    concat('Conversation ID: ', evaldata.conversationid) AS "Title Conv",
    evaldata.evaluationid,
    concat('Eval ID: ', evaldata.evaluationid) AS "Title Eval",
        CASE
            WHEN (evaldata.calibrationid IS NULL) THEN false
            ELSE true
        END AS iscalibration,
    evaldata.evaluationformid,
    evaldata.evaluatorid,
    concat('Evaluator: ', vwuserdetail.name) AS "Title Evaluator",
    evaldata.userid,
    evaldata.status,
    evaldata.totalscore,
    evaldata.totalcriticalscore,
    evaldata.totalnoncriticalscore,
    ((evaldata.agenthasread)::character varying)::boolean AS agenthasread,
    evaldata.releasedate,
    evaldata.releasedateltc,
    (evaldata.releasedateltc + ((( SELECT timezonecalcs.diff
           FROM timezonecalcs('Australia/Sydney'::text) timezonecalcs(utctime, ltctime, diff, timezonechosen)))::double precision * '00:00:01'::interval)) AS releasedateusrtz,
    evaldata.assigneddate,
    evaldata.assigneddateltc,
    userdetails.name AS "Agent Name",
    userdetails.department AS "Agent Dept",
    userdetails.managerid AS "Agent Manager Id",
    userdetails.managername AS "Agent Manager Name",
    userdetails.divisionid AS "Agent Div",
    vwuserdetail.name AS "Evaluator Name",
    vwuserdetail.managerid AS "Evaluator Manager Id",
    vwuserdetail.managername AS "Evaluator Manager Name",
    convsumm.conversationstartdate AS convstartdate,
    convsumm.conversationenddate AS convenddate,
    ((sum(evaldata.totalscore) / (count(evaldata.conversationid))::numeric))::integer AS average_score,
        CASE
            WHEN (evaldata.totalscore > (80)::numeric) THEN 1
            ELSE 0
        END AS over80,
    convsumm.firstqueuename AS queuename,
    convsumm.firstmediatype AS mediatype
   FROM (((evaldata evaldata
     LEFT JOIN vwuserdetail userdetails ON (((evaldata.userid)::text = (userdetails.id)::text)))
     LEFT JOIN vwuserdetail vwuserdetail ON (((evaldata.evaluatorid)::text = (vwuserdetail.id)::text)))
     LEFT JOIN vwconvsummarydata convsumm ON (((convsumm.conversationid)::text = (evaldata.conversationid)::text)))
  WHERE ((evaldata.status)::text = 'FINISHED'::text)
  GROUP BY convsumm.conversationenddate, convsumm.conversationstartdate, convsumm.firstmediatype, convsumm.firstqueuename, evaldata.agenthasread, evaldata.assigneddate, evaldata.assigneddateltc, evaldata.calibrationid, evaldata.conversationid, evaldata.evaluationformid, evaldata.evaluationid, evaldata.evaluatorid, evaldata.releasedate, evaldata.releasedateltc, evaldata.status, evaldata.totalcriticalscore, evaldata.totalnoncriticalscore, evaldata.totalscore, evaldata.userid, userdetails.department, userdetails.divisionid, userdetails.managerid, userdetails.managername, userdetails.name, vwuserdetail.managerid, vwuserdetail.managername, vwuserdetail.name
)

Columns

Name Type Default Nullable Children Parents Comment
keyid text true Primary Key
conversationid varchar(50) true Conversation GUID
Title Conv text true Title for Conversation
evaluationid varchar(50) true Evaluation GUID
Title Eval text true Title for Evaluation
iscalibration boolean true Evaluation is a Calibration
evaluationformid varchar(50) true Evaluation Form GUID
evaluatorid varchar(50) true Evaluator GUID
Title Evaluator text true Title for Evaluator
userid varchar(50) true User GUID
status varchar(20) true Status
totalscore numeric(20,2) true Total Score
totalcriticalscore numeric(20,2) true Total Critical Score
totalnoncriticalscore numeric(20,2) true Total Non-Critical Score
agenthasread boolean true Agent has read
releasedate timestamp without time zone true Release Date(UTC)
releasedateltc timestamp without time zone true Release Date in(LTC)
releasedateusrtz timestamp without time zone true Release Date in User Timezone
assigneddate timestamp without time zone true Assigned Date(UTC)
assigneddateltc timestamp without time zone true Assigned Date(LTC)
Agent Name varchar(200) true Agent Name
Agent Dept varchar(200) true Agent Department
Agent Manager Id varchar(50) true Agent Manager GUID
Agent Manager Name varchar(200) true Agent Manager Name
Agent Div varchar(50) true Agent Division
Evaluator Name varchar(200) true Evaluator Name
Evaluator Manager Id varchar(50) true Evaluator Manager GUID
Evaluator Manager Name varchar(200) true Evaluator Manager Name
convstartdate timestamp without time zone true Conversation Start Date
convenddate timestamp without time zone true Conversation End Date
average_score integer true Average Score
over80 integer true
queuename varchar(255) true Queue Name
mediatype varchar(50) true Media Type

Referenced Tables

Name Columns Comment Type
timezonecalcs 0
public.evaldata 20 BASE TABLE
public.vwuserdetail 17 See UserDetail: User Description in detail VIEW
public.vwconvsummarydata 66 See ConvSummaryData - Expands all the GUIDs with their lookups VIEW

Indexes

Name Definition
mvwevaluationoverviewkeyid CREATE UNIQUE INDEX mvwevaluationoverviewkeyid ON public.mvwevaluationoverview USING btree (keyid)
mvwevaluationoverviewmedia CREATE INDEX mvwevaluationoverviewmedia ON public.mvwevaluationoverview USING btree (mediatype)
mvwevaluationoverviewqueue CREATE INDEX mvwevaluationoverviewqueue ON public.mvwevaluationoverview USING btree (queuename)

    • Related Articles

    • 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 ...

    • public.userpresencedetaileddata

      Description User Presence Detailed Data Columns Name Type Default Nullable Children Parents Comment keyid varchar(255) false Primary Key userid varchar(50) true Agent GUID starttime timestamp without time zone false Start Time (UTC) starttimeltc ...

    • public.evalquestiondata

      Description Columns Name Type Default Nullable Children Parents Comment keyid varchar(50) false evaluationid varchar(50) false evaluationformid varchar(50) false questiongroupid varchar(50) true questionid varchar(50) true answerid varchar(50) true ...

    • public.userinteractionpresencedetaileddata

      Description Columns Name Type Default Nullable Children Parents Comment keyid varchar(255) false userid varchar(50) true starttime timestamp without time zone false starttimeltc timestamp without time zone true endtime timestamp without time zone ...

    • public.userpresencedata

      Description Columns Name Type Default Nullable Children Parents Comment keyid varchar(255) false id varchar(50) true userid varchar(50) true startdate timestamp without time zone false startdateltc timestamp without time zone true timetype ...