CREATE VIEW vwrealtimeuser_groups AS (
 SELECT rd.name AS user_name,
    rd.id AS user_id,
    rd.divisionid,
    rd.division_name,
    rd.groupid,
    rd.groupname,
    rd.managername,
    rd.managerid,
        CASE
            WHEN (rd.systempresence = 'ON_QUEUE'::text) THEN (rd.routingstatus)::text
            ELSE upper(replace(rd.orgpresence, 'On Queue'::text, 'ON_QUEUE'::text))
        END AS agentstatus,
        CASE
            WHEN (rd.systempresence = 'ON_QUEUE'::text) THEN rd.routstattime
            ELSE rd.presencetime
        END AS agenttime,
        CASE
            WHEN (rd.systempresence = 'ON_QUEUE'::text) THEN ((rd.routstattime)::double precision * '00:00:01'::interval)
            ELSE ((rd.presencetime)::double precision * '00:00:01'::interval)
        END AS agenttime_formatted,
    ((
        CASE
            WHEN (rd.systempresence = 'ON_QUEUE'::text) THEN rd.routstattime
            ELSE rd.presencetime
        END)::numeric / 86400.00) AS agenttimeday,
    rd.systempresence,
    rd.orgpresence,
    rd.routingstatus,
    rd.routstarttime,
    rd.routstattime,
    rd.routstattimeday,
    rd.presenceid,
    rd.presstarttime,
    rd.presencetime,
    rd.presencetimeday,
    rd.queuename,
    rd.conversationid,
    rd.media,
    rd.direction,
    rd.acwstate,
    rd.acwtime,
    rd.adherencestate,
    rd.adherencestarttime,
    rd.impact,
    rd.scheduledactivitycategory
   FROM ( SELECT rl.id,
            rl.name,
            gd.id AS groupid,
            gd.name AS groupname,
            ud.managerid,
            ud.managername,
            ud.divisionid,
            dd.name AS division_name,
            upper(replace((rl.systempresence)::text, 'On Queue'::text, 'ON_QUEUE'::text)) AS systempresence,
            upper(replace((pr.orgpresence)::text, 'On Queue'::text, 'ON_QUEUE'::text)) AS orgpresence,
                CASE
                    WHEN (((rl.routingstatus)::text = 'IDLE'::text) AND (((((rl.cccallactive + rl.othcallactive) + rl.cbcallactive) + rl.cbothcallactive) + rl.ccemailactive) > 0)) THEN 'ALERT'::character varying
                    ELSE rl.routingstatus
                END AS routingstatus,
            rl.routstarttime,
            datediff('second'::character varying, rl.routstarttime, now_utc()) AS routstattime,
            ((datediff('second'::character varying, rl.routstarttime, now_utc()))::numeric / 86400.00) AS routstattimeday,
            rl.presenceid,
            rl.presstarttime,
            datediff('second'::character varying, rl.presstarttime, now_utc()) AS presencetime,
            ((datediff('second'::character varying, rl.presstarttime, now_utc()))::numeric / 86400.00) AS presencetimeday,
            qd.name AS queuename,
            qd.id AS queueid,
            uc.conversationid,
            uc.media,
            uc.direction,
            uc.conversationstate,
            uc.acwstate,
            datediff('second'::character varying, uc.acwtime, now_utc()) AS acwtime,
            rl.adherencestate,
            rl.adherencestarttime,
            rl.impact,
            rl.scheduledactivitycategory
           FROM (((((((userrealtimedata rl
             LEFT JOIN presencedetails pr ON (((pr.id)::text = (rl.presenceid)::text)))
             LEFT JOIN userrealtimeconvdata uc ON (((uc.userid)::text = (rl.id)::text)))
             LEFT JOIN queuedetails qd ON (((qd.id)::text = (uc.queueid)::text)))
             LEFT JOIN vwuserdetail ud ON (((ud.id)::text = (rl.id)::text)))
             LEFT JOIN usergroupmappings ug ON (((ug.userid)::text = (rl.id)::text)))
             LEFT JOIN groupdetails gd ON (((gd.id)::text = (ug.groupid)::text)))
             LEFT JOIN divisiondetails dd ON (((dd.id)::text = (ud.divisionid)::text)))) rd
)