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