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
)| 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 |
| 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) |