Skip to main content
Solved

Total counter for a date occurrence within a range value from a different table

chrbalm
Contributor
Forum|alt.badge.img+8

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.

Best answer by takashi

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.

[Edited] 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

View original
Did this help you find an answer to your question?

4 replies

takashi
Supporter
  • takashiSupporter
  • Best Answer
  • March 14, 2022

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.

[Edited] 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

M
Forum|alt.badge.img+2

@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

chrbalm
Contributor
Forum|alt.badge.img+8
  • chrbalmContributor
  • Author
  • Contributor
  • March 22, 2022
takashi wrote:

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.

[Edited] 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!

chrbalm
Contributor
Forum|alt.badge.img+8
  • chrbalmContributor
  • Author
  • Contributor
  • March 22, 2022
markatsafe wrote:

@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


Helpful Members This Week

Community Stats

31,039
Posts
117,660
Replies
38,818
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.