[Solved] Average within dynamic daterange

[gkick]

Hi there,

I need to calculate an average of records for a dynamic date range that is I need to filter dates from today + 90 days, then do a standard between start and finish followed by using the average function
How do I get the end date x+90 into the query - storing the datediff result to a variable or is there some other way.

Thanks

[Villeroy]

Use function DATEADD as documented.

[Sliderule]

Since you are using HSQL 2.5.1 ( or any other version of HSQL since HSQL 2.X / NOT HSQL 1.8.0.10 - Embedded database HSQL ) . . . you can use the built-in functions, DATEADD as well as CURRENT_DATE .

Additionally:

1. The database back end ( HSQL 2.X ) can return a date from a VIEW ( for example, with a name of "CUR_DATE_90" ) using:
   

   Code: Select all

   Select DATEADD('day', 90, CURRENT_DATE ) as "CUR_DATE_90" From "INFORMATION_SCHEMA"."SYSTEM_TABLES" LIMIT 1

2. As a sub-query in your new Query

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[gkick]

@Sliderule and @Villeroy

Thank you gentlemen