How to check if date range is within a date range in the same columns

Question

Hi, I have a problem.

I want to check if for the same ID, there is overlapping Start Date and End Date (the Bolded Row) in table 1. I want it to fill the data of that bolded row to those that are applicable to result in table 2.

Table 1

IDStart DateEnd DatePeriodA1A2B1B2CD1D2E1E11114/1/20209/30/202010.70.7       11110/1/202012/31/202010.70.7       11110/1/20209/30/20211     0.30.3  1111/1/20219/30/20211  0.70.7

Table 2 (Desired Result)

IDStart DateEnd DatePeriodA1A2B1B2CD1D2E1E11114/1/20209/30/202010.70.7       11110/1/202012/31/202010.70.7   0.30.3  1111/1/20219/30/20211  0.70.7 0.30.3

How can I do that? Advanced thanks!

Tyro_abc · Answer

If you closely look at the original solution link, it is joining same table twice.  Same thing can be done here

clmc9601 · Answer

Hi @jt98,

This is a pretty lengthy solution (I'm sure someone can come up with a more efficient one), but it returns the desired output and is relatively dynamic. It uses append fields to create all pairs, so it'll take significant computing time/power for large datasets, which is a potential drawback. I hope it helps!

Date filtering.yxmd

jt98 · Answer

Hi, Table 2 is the result that i want so i cant join anything.