I have a table with various environmental data listed by year going back 30+ years. There is an event that occurs late each year in the Fall and I want to know what the average date is for this event for those 30+ years. The answer should be something like 15 November or similar, without the year. My current solution is most in-elegant (ugly) and I want to know if there is a better way.

In other words, what is the average month and day from the following:

2001-11-01
2002-12-06
2003-09-30
2004-10-23
...

Any help appreciated, thanks, Sean

I have no idea how to do it in SQL, but I'd start with the premise that you are
looking for the day of the year. Thus you need to convert each date to the Nth day
of the year. Averaging them is then "easy", and can then be converted back to a
date. Whether you pre-process the database to add the day of year alongside the
date or it can be done in SQL I do not know. Interesting problem!

/Matthew Jones/

Hi Mathew, I did setup a column for the Julian date and that has helped because I can basically calculate the date as the difference from Jan 1 of the year in question but I was wondering if anyone had something better or if there was something better. Cheers

(Matthew Jones) wrote:

I have no idea how to do it in SQL, but I'd start with the premise that you are
looking for the day of the year. Thus you need to convert each date to the Nth day
of the year. Averaging them is then "easy", and can then be converted back to a
date. Whether you pre-process the database to add the day of year alongside the
date or it can be done in SQL I do not know. Interesting problem!

/Matthew Jones/

Sean,

<< I have a table with various environmental data listed by year going back
30+ years. There is an event that occurs late each year in the Fall and I
want to know what the average date is for this event for those 30+ years.
The answer should be something like 15 November or similar, without the
year. My current solution is most in-elegant (ugly) and I want to know if
there is a better way.  >>

Just to clarify: do you want the *actual* day, as in "X number of days since
the beginning of the year", or just the *named day*, as in "Month/Day" ?

You can get the absolute day of the year via the EXTRACT(DAYOFYEAR FROM
xxxx) function:

http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=delphi&version=7&topic=Functions

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim, the end result is that I want to be able to state that of the past 20 years, the average date for this event to occur is November 20th or whatever that date happens to be. Thanks


"Tim Young [Elevate Software]" wrote:

Sean,

<< I have a table with various environmental data listed by year going back
30+ years. There is an event that occurs late each year in the Fall and I
want to know what the average date is for this event for those 30+ years.
The answer should be something like 15 November or similar, without the
year. My current solution is most in-elegant (ugly) and I want to know if
there is a better way.  >>

Just to clarify: do you want the *actual* day, as in "X number of days since
the beginning of the year", or just the *named day*, as in "Month/Day" ?

You can get the absolute day of the year via the EXTRACT(DAYOFYEAR FROM
xxxx) function:

http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=delphi&version=7&topic=Functions

Tim Young
Elevate Software
www.elevatesoft.com

Sean McDermott wrote:

All done, thanks. Settled on converting all dates to a Julian value based on the the Jan 1 Julian value for each individual year being examined. The Julian values were then averaged and then converted back into an average date (November 25th by the way) using Jan 1, 2014 as the reference. The display was then formatted to show MMM DD for the result. Cheers, Sean

Hi Tim, the end result is that I want to be able to state that of the past 20 years, the average date for this event to occur is November 20th or whatever that date happens to be. Thanks


"Tim Young [Elevate Software]" wrote:

Sean,

<< I have a table with various environmental data listed by year going back
30+ years. There is an event that occurs late each year in the Fall and I
want to know what the average date is for this event for those 30+ years.
The answer should be something like 15 November or similar, without the
year. My current solution is most in-elegant (ugly) and I want to know if
there is a better way.  >>

Just to clarify: do you want the *actual* day, as in "X number of days since
the beginning of the year", or just the *named day*, as in "Month/Day" ?

You can get the absolute day of the year via the EXTRACT(DAYOFYEAR FROM
xxxx) function:

http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=delphi&version=7&topic=Functions

Tim Young
Elevate Software
www.elevatesoft.com