Skip to main content
  • EN

I currently have 2 datasets - one is a set of records with a date attribute. The other is a schedule dataset with 2 attributes indicating the start and finish dates.

 

I am trying to identify how I can query whether a record in dataset one is within the range of the start and end dates of dataset two, and then subsequently count the number of times each record appears in the range of dates

 

The end result ideally is something similar to the statistics calculator where I would see each record with an extra attribute with a numerical value indicative of how many times that date has occurred in the range of the schedule dataset.

Hi @cbalmbra1​ , a possible way is to execute a SQL statement like this, with the InlineQuerier. Assuming that all the date values are formatted with "yyyy-mm-dd". See also the attached workspace example.

tEdited] Replace the SQL statement in the attached workspace example with this one.

select
    d2.id,
    d2.start,
    d2.finish,
    count(d1.date) count
from dataset_2 as d2
left outer join dataset_1 as d1
on d1.date between d2.start and d2.finish
group by d2.id

inlinequerier-example

@cbalmbra1​ FME table joiners (FeatureJoiner, FeatureMerger, etc) don't have the equivalent of a BETWEEN joins. Try the InlineQuerier. You can build a SQL BETWEEN there

Hi @cbalmbra1​ , a possible way is to execute a SQL statement like this, with the InlineQuerier. Assuming that all the date values are formatted with "yyyy-mm-dd". See also the attached workspace example.

tEdited] Replace the SQL statement in the attached workspace example with this one.

select
    d2.id,
    d2.start,
    d2.finish,
    count(d1.date) count
from dataset_2 as d2
left outer join dataset_1 as d1
on d1.date between d2.start and d2.finish
group by d2.id

inlinequerier-example

Awesome. Just the ticket. Thanks!

@cbalmbra1​ FME table joiners (FeatureJoiner, FeatureMerger, etc) don't have the equivalent of a BETWEEN joins. Try the InlineQuerier. You can build a SQL BETWEEN there

Awesome. Just the ticket. Thanks!

Reply


Community Stats

30,168
Posts
114,527
Replies
37,825
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.