See UserPresenceDataDaily - Expands all the GUIDs with their lookups(Daily)
Table Definition
CREATE VIEW vwuserpresencedatadaily AS (
SELECT userpresencedatadaily.id,
userpresencedatadaily.userid,
userdetail.name AS agentname,
userdetail.managerid,
userdetail.managername,
userdetail.divisionid,
dd.name AS division_name,
userpresencedatadaily.startdate,
(userpresencedatadaily.startdate + ((( SELECT timezonecalcs.diff
FROM timezonecalcs('Australia/Sydney'::text) timezonecalcs(utctime, ltctime, diff, timezonechosen)))::double precision * '00:00:01'::interval)) AS startdateusrtz,
userpresencedatadaily.timetype,
userpresencedatadaily.systempresenceid,
CASE
WHEN ((userpresencedatadaily.systempresenceid)::text ~ '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$'::text) THEN presencedetails.systempresence
ELSE userpresencedatadaily.systempresenceid
END AS systempresencename,
userpresencedatadaily.presenceid,
CASE userpresencedatadaily.timetype
WHEN 'Presence'::text THEN userpresencedatadaily.presencetime
ELSE (0)::numeric
END AS presencetime,
(userpresencedatadaily.presencetime / 86400.00) AS presencetimeday,
userpresencedatadaily.routingid,
CASE userpresencedatadaily.timetype
WHEN 'Routing'::text THEN userpresencedatadaily.presencetime
ELSE (0)::numeric
END AS routingtime,
(userpresencedatadaily.routingtime / 86400.00) AS routingtimeday,
userpresencedatadaily.updated
FROM (((userpresencedatadaily userpresencedatadaily
LEFT JOIN vwuserdetail userdetail ON (((userdetail.id)::text = (userpresencedatadaily.userid)::text)))
LEFT JOIN divisiondetails dd ON (((dd.id)::text = (userdetail.divisionid)::text)))
LEFT JOIN presencedetails ON (((presencedetails.id)::text = (userpresencedatadaily.systempresenceid)::text)))
)
Name | Type | Default | Nullable | Children | Parents | Comment |
---|---|---|---|---|---|---|
id | varchar(50) | true | Primary key | |||
userid | varchar(50) | true | User GUID | |||
agentname | varchar(200) | true | Agent Name | |||
managerid | varchar(50) | true | Manager GUID | |||
managername | varchar(200) | true | Manager Name | |||
divisionid | varchar(50) | true | Division GUID | |||
division_name | varchar(50) | true | Division Name | |||
startdate | timestamp without time zone | true | Start Date | |||
startdateusrtz | timestamp without time zone | true | Start Date in User Timezone | |||
timetype | varchar(50) | true | Time Type | |||
systempresenceid | varchar(50) | true | System Presence GUID | |||
systempresencename | varchar | true | System Presence Name | |||
presenceid | varchar(50) | true | Presence GUID | |||
presencetime | numeric | true | Presence Time | |||
presencetimeday | numeric | true | Presence Time (Days) | |||
routingid | varchar(50) | true | Routing GUID | |||
routingtime | numeric | true | Routing Time | |||
routingtimeday | numeric | true | Routing Time (Days) | |||
updated | timestamp without time zone | true | Last Updated Time |