How to create a filter for OVERLAPPING DATES

[Anonymous]

I have the following fields:

ProductID, Contract#, Contract status, Contract start date & contract end date. contracts are assigned only after one expires. Meaning, contracts should be extended only after the 1st one expires. So I am looking for duplicate contracts that were issued before the previous one expired and it can be captured by spotting overlapping dates. I was thinking about Interval match but I dont know where to start. any help would be great I have also attached excel sheet.

Many Thanks

[simsondevadoss]

I think you can use canonical date for this.

Please check this link : Canonical Date

[Anonymous]

My Expn is Count(distinct SID) other columns r just Dimensions

[#4 · Accepted solution]

Hi,

You could check for overlapping in the script.

temp_contr:

LOAD ProductID,

     [Contract #],

     [Contract Status],

     Date#([Contract Start Date],'M/D/YYYY') As [Contract Start Date],

     Date#([Contract End Date] ,'M/D/YYYY') As [Contract End Date]

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

temp2_contr:

LOAD

  ProductID,

     [Contract #],

     [Contract Status],

     [Contract Start Date],

     [Contract End Date]

RESIDENT temp_contr order by ProductID, [Contract Start Date], [Contract End Date];

contracts:

LOAD

  ProductID,

     [Contract #],

     [Contract Status],

     [Contract Start Date],

     [Contract End Date],

     If(ProductID = Peek(ProductID),If([Contract Start Date] < Peek([Contract End Date]), 1, 0),0) As Overlapping

RESIDENT temp2_contr;

Drop Tables temp_contr, temp2_contr;

[#5]

I just realized you could miss some lines by the method I suggested.

Maybe add another table with distinct ProductID and check for overlapping:

overlap:

LOAD DISTINCT

  ProductID,

  If(Sum(Overlapping) > 0, 1, 0) As check

Resident contracts Group by ProductID;

[Anonymous]

Thanks t h , I will use this approach and let you know how it goes

[#7]

Hi,

Is there any way to see all the overlapping contract ids for a particular product id instead of  showing that overlapped contract id alone in the above script?

eg:

product-id, contract-id  ,start-date ,end-date

p1, c1, 25/1/15, 29/1/15

p1, c2, 26/1/15, 25/2/16

p1, c3,23/2/16, 25/5/17

Here I would like to see all the contract ids as overlapped instead of c2 and c3.

Thanks,

Kiruthi