Hi Angela,
We are using Oracle Spatial as our Enterprise RDMS so the views are created inside schema level geodatabases in our Oracle db. Some of the scheduling stuff relies heavily on PL\SQL procedures/functions but in general, in the layer definition expression, we are are doing something like:
GLOBALID IN (SELECT GLOBALID FROM <<VIEW THAT RETURNS GLOBALIDs OF FEATURES MEETING SOME CONDITION>>)
In the screenshot below, which shows trash receptacles that need to be serviced (emptied), the Service Frequency (1) is used in conjunction with the last service record's date (2) by RECEPTACLESSERVICENEEDED_V (3) view which returns globalids of receptacles that need to be serviced based on service frequency and last service record.
Here's the SQL of the RECEPTACLESERVICENEEDED_V:
SELECT a.objectid,
a.framematrl,
a.surfmatrl,
a.TYPE,
a.receptshape,
a.load,
a.color,
a.owned,
a.capacity,
a.servicefrequency,
a.status,
a.needdgnrvw,
a.dgnrvwnotes,
a.notes,
a.globalid,
a.created_user,
a.created_date,
a.last_edited_user,
a.last_edited_date,
b.created_date lastservicedate,
a.servicefrequency,
b.nextdatecalculated nextservicedate,
a.se_anno_cad_data,
a.shape
FROM sdelittermgmt.receptacles a
JOIN
( -- RECEPTACLES WITH SERVICE RECORDS
SELECT receptguid,
servicefrequency,
receptacleservice.created_date,
DECODE (
servicefrequency,
'1D', TRUNC (receptacleservice.created_date + 1),
'3D', TRUNC (receptacleservice.created_date + 3),
'1W', TRUNC (receptacleservice.created_date + 7),
'2W', TRUNC (receptacleservice.created_date + 14),
'1M', TRUNC (
ADD_MONTHS (receptacleservice.created_date, 1)))
nextdatecalculated,
RANK ()
OVER (PARTITION BY receptguid
ORDER BY receptacleservice.created_date DESC)
rnk
FROM receptacleservice
JOIN receptacles
ON receptacles.globalid = receptacleservice.receptguid
-- RECEPTACLES WITHOUT SERVICE RECORDS
UNION ALL
SELECT globalid receptguid,
servicefrequency,
created_date,
SYSDATE - 1 nextcalculateddate,
1 rnk
FROM receptacles a
WHERE NOT EXISTS
(SELECT receptguid
FROM receptacleservice b
WHERE b.receptguid = a.globalid)) b
ON (receptguid = globalid)
WHERE a.status = 'ACTIVE'
AND b.rnk = 1
AND SYSDATE > b.nextdatecalculated;
Hope this helps!