I am aware there are posts on this in the community and that there is a macro available to do this on the public gallery, however I think this is such important functionality that it should be incorporated into the main product. I want to be able to join 2 data sources by a date range. In SQL the code would look like this:
select ric.*,map.*
from
Staging.TicksHourlySummary ric
LEFT OUTER JOIN Reference.ReutersInstrumentCodeMap map
on (
ric.#RIC = map.ReutersInstrumentCode
and ric.[Datetime] >= map.EffectiveFromDate
and ric.[Datetime] < map.EffectiveToDate
)