How to filter date-range entries by date range

goedible · June 17, 2024, 3:17am

I created a table “daterange” with “startmonth”, “startday”, “endmonth”, “endday”. How can I use the data from “daterange” to filter the results from “annual_cultivation_dates_view”?

In a previous post I asked how-to-filter-date-range-entries-by-todays-date-or-another-date and this code worked: Maybe for someone with an better ability than me you can edit this code to include dates from “daterange” instead of current_date.

select
	*
from
	"annual_cultivation_dates_view"
where
	current_date between
		cast(
			year(current_date)
			|| '-' ||
			right('0' || "start_month", 2)
			|| '-' ||
			right('0' || "start_day", 2)
		as date)
		and
		cast(
			case
				when "start_month" <= "end_month" then
					year(current_date)
				else
					year(current_date) + 1
			end
			|| '-' ||
			right('0' || "end_month", 2)
			|| '-' ||
			right('0' || "end_day", 2)
		as date)

RobertG · June 17, 2024, 6:48am

A example database with table, a little bit content and the view might help here to understand …

fpy · June 17, 2024, 7:09am

… was there

goedible · June 17, 2024, 7:26am

I can not screenshot in design view because it gives me sql syntax error. Only sql view works.
You can download the database from the dropbox link that fpy posted.
I’m trying to create a new query that will return results that are within the range of dates in the “daterange” table instead of being within current_date.

goedible · June 17, 2024, 7:30am

goedible · June 17, 2024, 7:32am

RobertG · June 17, 2024, 8:58am

@fpy : Example: This isn’t a “little example” for me. I have to change to much to get it working with a normal Base installation. I expect a Base file with internal database, not an external connected HSQLDB.

goedible · June 17, 2024, 9:26am

thank you for your help and comments. I found a solution and posted it below.

fpy · June 17, 2024, 6:44am

...
  from
	"annual_cultivation_dates_view", "daterange"

more details from Join Properties

goedible · June 17, 2024, 7:45am

This seems to be working.

select
*
from
“annual_cultivation_dates_view”, “daterange”
where
cast(
year(current_date)
|| ‘-’ ||
right(‘0’ || “startmonth”, 2)
|| ‘-’ ||
right(‘0’ || “startday”, 2)
as date)
between
cast(
year(current_date)
|| ‘-’ ||
right(‘0’ || “start_month”, 2)
|| ‘-’ ||
right(‘0’ || “start_day”, 2)
as date)

	and

		cast(
			case
				when "start_month" <= "end_month" then
					year(current_date)
				else
					year(current_date) + 1
			end
			|| '-' ||
			right('0' || "end_month", 2)
			|| '-' ||
			right('0' || "end_day", 2)
		as date)
AND
	cast(
			year(current_date)
			|| '-' ||
			right('0' || "endmonth", 2)
			|| '-' ||
			right('0' || "endday", 2)
		as date)
	between
		cast(
			year(current_date)
			|| '-' ||
			right('0' || "start_month", 2)
			|| '-' ||
			right('0' || "start_day", 2)
		as date)

	and

		cast(
			case
				when "start_month" <= "end_month" then
					year(current_date)
				else
					year(current_date) + 1
			end
			|| '-' ||
			right('0' || "end_month", 2)
			|| '-' ||
			right('0' || "end_day", 2)
		as date)