topic Re: Only display points if today is Monday through Friday. in Mapping Questions

Only display points if today is Monday through Friday.

NinaRihn — Thu, 20 Apr 2017 16:21:05 GMT

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.

Re: Only display points if today is Monday through Friday.

Robert_LeClair — Thu, 20 Apr 2017 16:37:15 GMT

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?

Re: Only display points if today is Monday through Friday.

NinaRihn — Thu, 20 Apr 2017 21:17:07 GMT

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.

Re: Only display points if today is Monday through Friday.

DarrenWiens2 — Thu, 20 Apr 2017 21:33:34 GMT

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).

Re: Only display points if today is Monday through Friday.

RichardDaniels — Thu, 20 Apr 2017 21:37:45 GMT

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

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.

Re: Only display points if today is Monday through Friday.

NinaRihn — Mon, 24 Apr 2017 16:34:10 GMT

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