Skip to main content
Question

How to compare date ranges?

  • September 10, 2020
  • 4 replies
  • 114 views

Hi all,

 

I have a data set with a start_date column and an end_date column. What I need to do is determine which date ranges overlap, similar to this:

 

https://www.get-digital-help.com/find-overlapping-date-ranges-in-excel/" alt="http://https://www.get-digital-help.com/find-overlapping-date-ranges-in-excel/" target="_blank">https://www.get-digital-help.com/find-overlapping-date-ranges-in-excel/

 

I am fairly new to FME so I apologise in advance!

4 replies

ebygomm
Influencer
Forum|alt.badge.img+32
  • Influencer
  • September 10, 2020

I would probably do this spatially. Convert the dates to seconds, then create lines from start to end date, then check for spatial overlaps. I've attached a quick example

 


  • Author
  • September 11, 2020
ebygomm wrote:

I would probably do this spatially. Convert the dates to seconds, then create lines from start to end date, then check for spatial overlaps. I've attached a quick example

 

Great, many thanks for this!


takashi
Influencer
  • September 12, 2020

Here is another approach with an InlineQuerier.inlinequerier-parameters

SQL Query

select
    a.*,
    case
        when 0 < count(b.Name) then 'TRUE'
        else 'FALSE'
    end as Overlapping
from source as a
left outer join source as b
on b.Name <> a.Name and b.start <= a.end and a.start <= b.end
group by a.Name

 


takashi wrote:

Here is another approach with an InlineQuerier.inlinequerier-parameters

SQL Query

select
    a.*,
    case
        when 0 < count(b.Name) then 'TRUE'
        else 'FALSE'
    end as Overlapping
from source as a
left outer join source as b
on b.Name <> a.Name and b.start <= a.end and a.start <= b.end
group by a.Name

 

Hi Takashi,

Thanks for sharing this method - works for me, but partially. Is it possible to define pairs of overlapped records by time periods in output? I presume this should be additional join in query. I need this for further spatial analysis to see if these pairs or maybe there will be more overlapped records intersects with each other spatially. 

 

Thank you in advance!


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