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,
    qr.conversationstate,
    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
)