Join where date is between start and end dates?


(Danny Ryan) #1

Hi,

Does anyone know how to configure the join component to do the following;

SQL example
SELECT a.event_date
, b.start_date
, b.end_date
from events a
left join date_range b
on a.event_date between b.start_date and b.end_date
;

Where table ‘a’ contains a list of event dates, and table ‘b’ contains a series of date ranges. I want to return the date range per event date.

table ‘a’

event_date
01-SEP-2017
02-SEP-2017
15-SEP-2017
30-SEP-2017

table ‘b’

start_date end_date
01-SEP-2017 05-SEP-2017
15-SEP-2017 31-SEP-2017

Many thanks
Danny


#2

Hi Danny,

Well, Join is not capable of this. What you can do is:

  1. Join on equality fields (if there is any in your tables) or do just do cartesian join on 1=1
  2. Then using filter step apply a.event_date between b.start_date and b.end_date condition.

The downside of this solution is that cartesian join would produce a lot of rows, so if your tables are really big may be quite resource consuming.
By may ve you can make additional assumptions regarding the data, e.g. that data range couldn’t span for more than a month, so in join step you could join events and ranges on a month-year part.