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

    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?

       

      Thank you in advance!