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
[Solved] Average within dynamic daterange
[Solved] Average within dynamic daterange
Last edited by gkick on Sat Aug 22, 2020 4:20 pm, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Average within dynamic daterange
Use function DATEADD as documented.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Average within dynamic daterange
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:
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Additionally:
- 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
- As a sub-query in your new Query
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Average within dynamic daterange
@Sliderule and @Villeroy
Thank you gentlemen
Thank you gentlemen
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend