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!

[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!

[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]

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,

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

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

[WesMiller]

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

[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!!!

[ChrisSmith7]

Emily,

When you mention that "...if a events occurs every Tuesday starting 3/15/2015 and ends 12/31/2015, my current method will miss it" - it clarifies everything! I'm checking-out the data now to see the best way to pull these records.