Intervalmatch?

[#1]

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?

Thank you in advance!

[its_anandrjs · Accepted solution]

Hi,

Make another column or add another expression like

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

in the expression

Rgds

Anand

[its_anandrjs]

Hi,

See the attached sample file

HTH

Let me know about this.

Rgds

Anand

[#4]

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

[its_anandrjs]

Hi,

Just add another expression

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

to check parallel activity

Rgds

Anand

[kaushiknsolanki]

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

[#7]

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é

[its_anandrjs]

Hi esmeschelling,

Thanks i am glad to help you and thanks kaushik to explaining the objective

Rgds

Anand