Detailed user presence data with lookup expansion
CREATE VIEW vwuserpresencedetaileddata AS (
SELECT upd.keyid,
upd.userid,
ud.name AS agentname,
ud.managerid,
ud.divisionid,
ud.managername,
upd.starttime,
upd.starttimeltc,
upd.endtime,
upd.endtimeltc,
upd.systempresence,
upd.orgpresence,
upd.routingstatus,
upd.timeinstate,
(upd.timeinstate / 86400.00) AS timeinstateday,
upd.updated,
upd.organizationpresenceid AS organization_presence_id,
ud.divisionname AS division_name
FROM (userpresencedetaileddata upd
LEFT JOIN vwuserdetail ud ON (((ud.id)::text = (upd.userid)::text)))
)| Name | Type | Default | Nullable | Children | Parents | Comment |
|---|---|---|---|---|---|---|
| keyid | varchar(255) | true | Primary Key | |||
| userid | varchar(50) | true | User GUID | |||
| agentname | varchar(200) | true | Agent Name | |||
| managerid | varchar(50) | true | Manager GUID | |||
| divisionid | varchar(50) | true | Division GUID | |||
| managername | varchar(200) | true | Manager Name | |||
| starttime | timestamp without time zone | true | Start Time(UTC) | |||
| starttimeltc | timestamp without time zone | true | Start Time in LTC | |||
| endtime | timestamp without time zone | true | End Time(UTC) | |||
| endtimeltc | timestamp without time zone | true | End Time in LTC | |||
| systempresence | varchar(50) | true | System Presence | |||
| orgpresence | varchar(50) | true | Organization Presence | |||
| routingstatus | varchar(50) | true | Routing Status | |||
| timeinstate | numeric(20,2) | true | Time in State | |||
| timeinstateday | numeric | true | Time in State (Days) | |||
| updated | timestamp without time zone | true | Last Updated Time | |||
| organization_presence_id | varchar(50) | true | Organization Presence GUID | |||
| division_name | varchar(50) | true | Division Name |
| Name | Columns | Comment | Type |
|---|---|---|---|
| public.userpresencedetaileddata | 12 | User Presence Detailed Data | BASE TABLE |
| public.vwuserdetail | 18 | User details with manager and division context | VIEW |