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!
+21
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!
+17
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!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.