nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Select features based on daily and ongoing dates

EmilyLaMunyon

Hello,

I am pulling information from a web service in which I parse out the data to be stored in a 10.2 file geodatabase and then displayed on a web map showing only those events occurring for the next week from the current date. Most events are only one day, so this works fine. The problem I have is that some events are ongoing and if I pull from the start date field, I eventually lose them because it is 7 days past the current date. There is an EndDate field, so those events that are ongoing will have a different start and end date, I am just not sure how to represent not only features that start on the current date plus a week out, but also those that are ongoing between their start and end date. I am using the feature class to feature class tool in model builder with the following expression to create the feature class showing weekly events:

StartDate <= (CURRENT_DATE + 7) AND StartDate >= CURRENT_DATE

Is there a way to also select those features that are ongoing from the current date using the StartDate and EndDate fields?

Thanks for any advice!

Trouver plus de publications taguées avec

Accepted answers

WesMiller

I haven't tested but this should work

("StartDate" >= CURRENT_DATE AND "StartDate" <= CURRENT_DATE + 7) OR ("EndDate" >= CURRENT_DATE AND "EndDate" <= CURRENT_DATE +7) OR ("StartDate" <= CURRENT_DATE AND "EndDate" >= CURRENT_DATE +7)

All comments

EmilyLaMunyon

Hi Wes,

Yes, I am sorry it has been a few days. Here is a spreadsheet showing a sample of the events I would like to create a feature class from. The StartDate and EndDate fields are what I have been using to pull those events starting daily and occuring a week out. As I mentioned above, the problem with this is that I miss ongoing events that may happen in a given week due to the fact that their start date or end date does not fall within the range. For example, if a events occurs every Tuesday starting 3/15/2015 and ends 12/31/2015, my current method will miss it.

Any ideas would be greatly appreciated!!!

statuslog.csv.zip

Screenshot 2026-04-14 at 1.57.43 PM.png

WesMiller

Do you have a sample of data you'd be willing to post?

EmilyLaMunyon

Hi Wes,

Thanks for your response, this did not work for me as a definition query on a feature. Maybe I am missing something.

EmilyLaMunyon

Hi Chris,

The query you suggested does work for pulling everything within the time slice of a week, including those that start within a week from the current date or are ongoing through this time and this may be the route I take. I might be missing something, but I think there are really only 2 ways to display this data, either pull everything that overlaps a certain week:

EndDate >= CURRENT_DATE AND StartDate <= (CURRENT_DATE + 7)

or pull only those that have start dates in the certain week, regardless of the end dates.

StartDate <= (CURRENT_DATE + 7) AND StartDate >= CURRENT_DATE

If I go with the second option, this is what I am trying to achieve, pull all events starting today (8/19) through the next 7 days, regardless of their end date.
Event 1 - starts 8/19/2015; ends 10/31/2015 - capture

Event 2 -starts 8/20/2015; ends 8/20/2015 - capture

Event 3 - starts 8/21/2015; ends 8/22/2015 - capture

Event 4 - starts 8/22/2015; ends 8/24/2015-capture

Event 5 -starts 8/26/2015; ends 9/5/2015-capture

Event 6-starts 8/29/2015; ends 10/1/2015 (do not capture yet, should be captured when the script runs in this timeframe).

Event 6 - starts 7/31/2015; ends 9/3/2015- (do not capture)

Let me know if you can think of any other query that may work for what I am trying to accomplish.

Thanks so much again!!

WesMiller

Try the following query

fldname BETWEEN DateAdd(Day, 7, getdate()) AND getdate()

ChrisSmith7

Just curious, those records that are getting returned from January, is the end date still in the future? If so, wouldn't that would put them as "ongoing" within the week time slice - you do not want to return these, right? Maybe it would help to visualize it:

Week 8/24/2015 - 8/30/2015 (assuming today was 8/24 using CURRENT_DATE + 7😞

event 1 - starts 7/1/2015; ends 9/2/2015 (do not capture)

event 2 - starts 8/24/2015; ends 8/30/2015 (capture)

event 3 - starts 8/26/2015; ends 8/26/2015 (capture)

event 4 - starts 8/25/2015; ends 8/29/2015 (capture)

event 5 - starts 8/27/2015; ends 9/3/2015 (capture?)

event 6 - starts 8/23/2015; ends 8/27/2015 (do not capture)

event 7 - starts 8/1/2015; ends 8/22/2015 (do not capture)

event 8 - starts 8/31/2015; ends 8/31/2015 (do not capture)

event 9 - starts 9/3/2015; ends 9/9/2015 (do not capture)

Feel free to adjust the scenarios for capturing the events - this will give me a better idea at what you're looking to pull.

EmilyLaMunyon

Hi Chris,

First of all, thank you so much for your time on this. I think this is close to what I am trying to accomplish. When I use:

EndDate >= CURRENT_DATE AND StartDate <= (CURRENT_DATE + 7)

it seems to return all records that have a StartDate before 7 days from the CURRENT_DATE. I am getting events back to January. I am trying to think of a way to isolate it to just events starting today or in the next 7 days.

I really appreciate your input!!

ChrisSmith7

Emily,

From what I gather, you want to select events occurring within the next week (from the current date); you are encountering issues with ongoing events taking place during that slice not getting returned. If my understanding is correct, I would suggest the following...

In SQL Server, I would do something like this:

select *
from now_playing np
where (np.EndDate >= Getdate()
  and np.StartDate <= (Getdate()+7))

I think the where clause in the tool would look something like this:

EndDate >= CURRENT_DATE  AND StartDate <= (CURRENT_DATE + 7)

This returns records where the end date is today or any point in the future AND ALSO the start date is on or before 7 days in the future. The combination of both will return ongoing events in addition to any starting/ending within the time slice. In other words, the query *should* accomplish the following (I haven't had a chance to test this):

* A now playing record can begin within the next week (the end date is inconsequential)

* A now playing record can end within the next week (the start date is inconsequential)

* A now playing record can start today and end 7 days from now (spans the entire time slice of interest)

* A now playing record can start and end at any point within the time slice

Let me know if this works for you!

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.