Hi All,

We have a database that is tracking wild animals that have been released into the wild (radio collared) and requires a lot of calculations based on date data.

We are stumped on how to do the following as cleanly as possible:

Calculate the number of days to date in a season that an animal has been in the wild.

 

For Example:

Table 1

Animal Released Date

Today's Date

Calc_Days released Wet Hot Season

Calc_Days released Dry Cold Season

Calc_Days release Dry Hot Season

Table 2

Month | Season | num days in season

January | Wet Hot | 31

February | Wet Hot | 28.25

March | Wet Hot | 31

April | Wet Hot | 30

May | Dry Cold | 31

etc...for all months

 

A work around we found is to just create a date table with every date from our past data set until say 2020 and thien script it out to filter by a date range and return counts based on found sets.  That seems like it will work but it would be much cleaner to calcuate it so we don't need to fire a script (which could take up resources) each time to refresh the data.

Thanks in advance.

See if this thread helps you:

I am not sure I understand exactly what needs to be calculated here. How would you calculate this if you only had paper and pencil? Suppose an animal was released on December 15, 2013 and today is June 15, 2015 - what are the expected results, and why?

P.S. Please provide the full table of which month belongs to which season, AND update your profile to reflect your version. This could require a recursive calculation, which is only possible with a custom function.

 

Thanks for the replies...I'm new to posting on the forum.

We want an output that looks like this

Cheetah #1

Released into wild Date = Dec 15 2012

Current Date = Today

Field1 - Days_Since Release until Current Date in Wet Hot Season = x

Field2 - Days_Since Release until Current Date in Dry Cold Season = y

Field3 - Days_Since Release until Current Date in Dry Hot Season = z

Table defining seasons - Attached.

 

I hope this makes more sense...feel free to let me know any questions.

...feel free to let me know any questions.

You have not yet answered the questions I already asked. As it is, we are left largely guessing. If I am guessing correctly, you want to do something like the attached.

Note:

1. Your situation is simplified by not having seasons that cross a month or a year boundary; the proposed solution takes advantage of that (i.e. it will not work otherwise);
2. Note the script trigger that populates the global gPeriodID field in the Seasons table, so that the calculations refer to the currently viewed record in Periods.

 

SeasonRanges.fp7

Thanks Comment.  You nailed it.  That is the result we were looking for.  I'll have to parse the custom function a little more to understand the method, but it works for our needs as of now.

Thanks again!

You're welcome. It just occurred to me that since your seasons are essentially quadrimesters of the calendrical year, this could be simplified even further:

QuadrimesterRanges.fp7