public.vwrealtimequeue

public.vwrealtimequeue

Description

Real-time queue data

Table Definition

CREATE VIEW vwrealtimequeue AS (
 WITH all_media AS (
         SELECT DISTINCT queuerealtimeconvdata.media
           FROM queuerealtimeconvdata
        UNION
         SELECT unnest(ARRAY['voice'::text, 'callback'::text, 'chat'::text, 'message'::text, 'email'::text]) AS media
        ), queue_media AS (
         SELECT qd.id AS queueid,
            qd.name AS queuename,
            qd.divisionid,
            qd.divisionname,
            m.media
           FROM (vwqueuedetails qd
             CROSS JOIN all_media m)
        )
 SELECT qm.queueid,
    qm.queuename,
    qm.divisionid,
    qm.divisionname,
    qm.media,
    sum(
        CASE
            WHEN ((qr.actingas)::text = 'acd'::text) THEN 1
            ELSE 0
        END) AS acd_count,
    sum(
        CASE
            WHEN ((qr.actingas)::text = 'agent'::text) THEN 1
            ELSE 0
        END) AS interacting,
    COALESCE(min(
        CASE
            WHEN ((qr.actingas)::text = 'acd'::text) THEN qr.startdate
            ELSE NULL::timestamp without time zone
        END)) AS startdate,
    COALESCE(min(
        CASE
            WHEN ((qr.actingas)::text = 'acd'::text) THEN qr.startdateltc
            ELSE NULL::timestamp without time zone
        END)) AS startdateltc,
    datediff('second'::character varying, min(
        CASE
            WHEN ((qr.actingas)::text = 'acd'::text) THEN qr.startdate
            ELSE NULL::timestamp without time zone
        END), timezone('utc'::text, now())) AS statussecs,
    ((datediff('second'::character varying, min(
        CASE
            WHEN ((qr.actingas)::text = 'acd'::text) THEN qr.startdate
            ELSE NULL::timestamp without time zone
        END), timezone('utc'::text, now())))::numeric / 86400.00) AS statusdays,
    max(qr.updated) AS updated
   FROM (queue_media qm
     LEFT JOIN queuerealtimeconvdata qr ON ((((qr.queueid)::text = (qm.queueid)::text) AND ((qr.media)::text = (qm.media)::text))))
  GROUP BY qm.queueid, qm.queuename, qm.divisionid, qm.divisionname, qm.media, qr.conversationstate
  ORDER BY qm.queuename, qm.media
)

Columns

Name Type Default Nullable Children Parents Comment
queueid varchar(50) true Queue GUID
queuename varchar(255) true Queue name
divisionid varchar(50) true Division ID
divisionname varchar(50) true Division name
media varchar true Type of media (voice, chat, email, etc.)
acd_count bigint true Count of interactions with actingas = acd
interacting bigint true Total count of interactions currently being handled by agents
startdate timestamp without time zone true Start date of the interaction filtered to acd
startdateltc timestamp without time zone true Logical timestamp counter for start date filtered to acd
statussecs integer true Status duration in seconds calculated with DATEDIFF
statusdays numeric true Status duration in days calculated with DATEDIFF
updated timestamp without time zone true Timestamp of the last update

Referenced Tables

Name Columns Comment Type
public.queuerealtimeconvdata 32 BASE TABLE
public.vwqueuedetails 24 See QueueDetails: Queue Lookup data VIEW
queue_media 0

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