Only display points if today is Monday through Friday.

1015
5
04-20-2017 09:21 AM
NinaRihn
Occasional Contributor III

I have an MXD  that displays open offices based on the  definition query:  

Hours.Open < CURRENT_TIME and Hours.Close > CURRENT_TIME

I would like to make it so the points don't display on Saturday and Sunday and only display Mon through Friday (since they are all only open on weekdays).   Is this possible?

This mxd is ultimately used in a web service served by an AGOL map, so if there is a way to filter this in AGOL that would be acceptable.

0 Kudos
5 Replies
Robert_LeClair
Esri Notable Contributor

If the point feature class also has a field, for example Mon_Fri, using a code/numeric format, then yes.  For example, if the Mon_Fri attribute field is binary 1 or 0, 1 being open, 0 being closed, then add to your Definition Query Mon_Fri = 1.  The points displayed will be daily hours and M-F offices.  Make sense?

0 Kudos
NinaRihn
Occasional Contributor III

actually I only want the points to even show up at all if TODAY (AKA the current date/day of the week)  is Monday, Tues, Wed, Thurs or Friday.    

0 Kudos
DarrenWiens2
MVP Honored Contributor

I don't think there is an easy way to do this with a definition query. The way you would think this would work would be something like:

EXTRACT(DOW FROM CURRENT_TIMESTAMP) >= "FIRSTDAYOPEN" AND EXTRACT(DOW FROM CURRENT_TIMESTAMP) <= "LASTDAYOPEN"

...which relies on a column populated for first and last open (Monday = 1 and Friday = 5) and compares the current DOW (day of week) to those values. However, ArcGIS's flavour of SQL only supports YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND (not DOW).

0 Kudos
RichardDaniels
Occasional Contributor III

If ArcGIS full supported T-SQL we could just do WHERE DATENAME(weekday, DueDate) <> 'Sunday' 

or

DATENAME(weekday,GETDATE()) = 'Thursday'

https://technet.microsoft.com/en-us/library/ms174395(v=sql.105).aspx

If your data is in a database, I would suggest calculating the Day there and save the result into a new field, then use a method similar to the one described above for mapping.

NinaRihn
Occasional Contributor III

OK so here's what I did...I created a view in my database where there is a record for each office, and fields for the current date, current day of the week, and current time.

Then in my MXD, I joined my office locations layer to this view by Office ID, and in my query, I included in my definition query the following:  CurrDayOfWeek <> 'Saturday' and CurrDayOfWeek <> 'Sunday'

The view definition was:

SELECT OFFICE_NO, CURRENT_DATE AS CurrDate, TO_CHAR(sysdate, 'DAY') AS CurrDayOfWeek, ((TO_CHAR(CURRENT_TIMESTAMP,'HH12:MI AM'))) AS CurrTime
FROM SDE.FEATCLASSNAME