See ScheduleData - Expands all the GUIDs with their lookups
CREATE VIEW vwscheduledata AS (
SELECT sc.scheduleid,
sc.userid,
sc.shiftid,
sc.shiftstartdate,
sc.shiftstartdateltc,
sc.shiftlengthtime,
sc.activitystartdate,
sc.activitystartdateltc,
sc.activitylengthtime,
sc.activitydescription,
sc.activitycodeid,
sc.activitypaid,
sc.shiftmanuallyeditted,
ud.name AS agentname,
ud.managerid,
ud.managername,
ad.name AS activitycodedesc,
COALESCE(NULLIF((ad.name)::text, ''::text), (ad.category)::text) AS activitycodename
FROM ((scheduledata sc
LEFT JOIN vwuserdetail ud ON (((ud.id)::text = (sc.userid)::text)))
LEFT JOIN activitycodedetails ad ON ((((ad.businessunitid)::text = (sc.buid)::text) AND ((ad.id)::text = (sc.activitycodeid)::text))))
)
Name | Type | Default | Nullable | Children | Parents | Comment |
---|---|---|---|---|---|---|
scheduleid | varchar(50) | true | Schedule GUID | |||
userid | varchar(50) | true | User GUID | |||
shiftid | integer | true | Shift GUID | |||
shiftstartdate | timestamp without time zone | true | Start date of the shift | |||
shiftstartdateltc | timestamp without time zone | true | ||||
shiftlengthtime | integer | true | Length of the shift | |||
activitystartdate | timestamp without time zone | true | Start date of the activity | |||
activitystartdateltc | timestamp without time zone | true | Start date of the activity in local time | |||
activitylengthtime | integer | true | Length of the activity | |||
activitydescription | varchar(200) | true | Activity Description | |||
activitycodeid | varchar(50) | true | Activity code GUID | |||
activitypaid | bit(1) | true | Activity paid or not | |||
shiftmanuallyeditted | bit(1) | true | Shift was manually edited or not | |||
agentname | varchar(200) | true | Agent Name | |||
managerid | varchar(50) | true | Manager GUID | |||
managername | varchar(200) | true | Name of the manager | |||
activitycodedesc | varchar(50) | true | Description of the activity code | |||
activitycodename | text | true | Name of the activity code |
Name | Columns | Comment | Type |
---|---|---|---|
public.scheduledata | 16 | BASE TABLE | |
public.vwuserdetail | 17 | See UserDetail: User Description in detail | VIEW |
public.activitycodedetails | 11 | Activity Code Lookup Data | BASE TABLE |