topic Re: Inbox Query using date field in ArcGIS Survey123 Questions

Inbox Query using date field

Slamon — Mon, 13 Jan 2025 17:53:18 GMT

I need some help setting up a query or refining a query for the following logic.

AVO_LASTDATE is the last inspection date

I only want to show inspections where AVO_LASTDATE is greater than or equal to 15 days ago, but not in this month of this year.

For example, if the AVO_LASTDATE is 1/4/2025, I shouldn't see it in the inbox until 2/1/2025.

If AVO_LASTDATE is 1/31/2025, I should see it in the inbox starting 2/15/2025

Inspections have to be at least 15 days apart but I can't do two in the same month. This is what I have now, but am getting a 400 error.

(CURRENT_DATE - 15 >= AVO_LASTDATE) AND MONTH(AVO_LASTDATE) != MONTH(CURDATE())
AND YEAR(AVO_LASTDATE = YEAR(CURDATE())

Re: Inbox Query using date field

DavidSolari — Mon, 13 Jan 2025 19:12:22 GMT

Your last 15 days logic is backwards, you're mixing CURRENT_DATE with CURDATE() (not a real function), those MONTH and YEAR functions won't work and you're testing for inequality with != instead of <>. Putting that all together with some help from this post:

AVO_LASTDATE > (CURRENT_DATE - 21) AND extract(month from AVO_LASTDATE) <> extract(month from CURRENT_DATE) AND extract(year from AVO_LASTDATE) = extract(year from CURRENT_DATE)

Re: Inbox Query using date field

Slamon — Mon, 13 Jan 2025 19:29:36 GMT

Thanks David,

This appears to have done the trick.

Re: Inbox Query using date field

Slamon — Tue, 28 Jan 2025 20:40:43 GMT

I ran into a bit of a snag with the EXTRACT (month....). There are no errors, but the query doesn't return any results. I tested it with EXTRACT (year....) and that is working fine. Any ideas on why it's not liking the month option?

This version works:

(ASSETTEAM = 'Eagle Ford') AND (AVO = 'Yes') AND (ROUTE_NUMBER = 'A1-101') AND (CURRENT_DATE - 15 >= AVO_LASTDATE) AND EXTRACT (year from AVO_LASTDATE) <> EXTRACT (year from CURRENT_DATE)

This version doesn't:

(ASSETTEAM = 'Eagle Ford') AND (AVO = 'Yes') AND (ROUTE_NUMBER = 'A1-101') AND (CURRENT_DATE - 15 >= AVO_LASTDATE) AND EXTRACT (month from AVO_LASTDATE) <> EXTRACT (month from CURRENT_DATE)

Ideally, I'd like to get something along the lines of:

(ASSETTEAM = 'Eagle Ford') AND (AVO = 'Yes') AND (ROUTE_NUMBER = 'A1-101') AND (CURRENT_DATE - 15 >= AVO_LASTDATE) AND EXTRACT (month from AVO_LASTDATE) <> EXTRACT (month from CURRENT_DATE) AND EXTRACT (year from AVO_LASTDATE) <> EXTRACT (year from CURRENT_DATE)