7 Replies Latest reply: Oct 7, 2011 3:52 AM by Anand Chouhan

# Intervalmatch?

Hi,

I'm trying to check whether a certain start date lies within a period of any other record in the table. Customers can buy different product form different department with different start and end dates. I would like to determine whether the start date of a product is overlapping with an existing product from that department.

For example:

CustID, ProductID, Department, StartDate, EndDate

1,38495,CHI, 20091201,20100105

1,38496,CHI, 20100131,20100431

2,45285,ORT,20080930,20081201

2,45275,ORT,20081005,20090125

2,58451,KNO,20081108,20090208

As you can see the second product of customer 2 at the ORT department started at 20081005 while the first product was still open. I would like to add an extra column which tells me for every product if it started while an other was still open, like this:

CustID, ProductID, Department, StartDate, EndDate, Parallel

1,38495,CHI, 20091201,20100105,0

1,38496,CHI, 20100131,20100431,0

2,45285,ORT,20080930,20081201,0

2,45275,ORT,20081005,20090125,1

2,58451,KNO,20081108,20090208,0

How can I make this work?

• ###### Re: Intervalmatch?

Hi,

Make another column or add another expression like

If( StartDate < Above(EndDate) , 1,0)

in the expression

Rgds

Anand

• ###### Re: Intervalmatch?

Hi,

See the attached sample file

HTH

Rgds

Anand

• ###### Intervalmatch?

Hi Anand,

Thank you for the answer! Sorry, I can not open the attachment: Personal Edition. Can you post it in the text field?!

Rgds,

Esme

• ###### Intervalmatch?

Hi,

If( StartDate < Above(EndDate) , 1,0)

to check parallel activity

Rgds

Anand

• ###### Intervalmatch?

HI,

Anand has created a straight chart with dimensions as CustID,ProductID and Dept.ID and expression as follows.

1. StartDate,

2. EndDate

3. If( StartDate < Above(EndDate) , 1,0)

Regards,

Kaushik Solanki

• ###### Intervalmatch?

Hi,

Thank you for your help. It works great!

Only a problem appeared when the product above is parallel, but not within the same department or for the same customer. Therefor I added a few extra conditions:

if(CustID=Above(CustID) and Depart=Above(Depart) and

StartDate < Above(Enddate), 'parallel','not-parallel')

Regards,

Esmé

• ###### Intervalmatch?