Skip to main content

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!

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

 


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!


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

 


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!


Reply