Flagging overlapping date ranges

krishnagandhi

Hi, 

I was trying to figure out how to flag a set of data where date ranges overlap. I have the following data:

Now what i am trying to do is whenever i have the same contract#, product# & condition code i want to take the validity dates and check if there are any date overlaps. I created a workflow that groups the data for me into contract/product/condition code...as you can see below i have 3 unique groups

I want to be able to flag group 2 & 3 as they have overlapping dates... this will help me as i have thousands of lines of data to go through and manually trying to figure our if there are overlaps is not a good method. 

I have attached the workflow that i used to get the grouping... but i'm unsure how to proceed from there to help me flag the groups with dates that overlap each other (group 2 & 3 in my example).

Thanks,

Krishna

Date Overlap.yxmd

MarqueeCrew

@krishnagandhi ,

Instead of building a workflow to solve your challenge, may I please direct you into how to think and construct it yourself?  As you state, you're looking for cases where:

1.  Contract is the same

2.  Multiple groups exist

3.  Dates are not unique 

Using a GENERATE ROWS tool (or my CReW Generate Date Rows Macro ), you can create individual records for every date in the range of From/To.

A SUMMARIZE TOOL can now be configured to GroupBy Contract, and DATE with a DistinctCount of Groups.

If the DistinctCount > 1, there is an overlap.

Cheers,

Mark