6 Replies Latest reply: Feb 5, 2014 5:33 AM by Adam Sovák RSS

    Double interval match?

      Hi,

       

      I've got this problem. (example data in xls file)

       

      There is data table with date, customers and their loans.

      DATA:

      CustomerLoanDate
      A41000

      1.1.2012

      A410001.2.2012
      A410001.3.2012
      A410001.4.2012
      A415001.5.2012
      A415001.6.2012

       

      Then I've got table with CUSTOMER_TYPE:

      Customer Type Valid_fromValid_to
      A4L11.1.2012
      A4L21.4.2012

       

      And then I have two tables of Loans rates. One for Customer TYPE_RATE

      TypeInterest_rateValid_FromValid_to
      L10,051.1.2012
      L10,11.3.2012
      L20,61.1.2012
      L20,71.2.2012

       

      and another for special customer rates

      CustomerSpecial_Interest_rateValid_fromValid_to
      A40,0115.1.201215.2.2012

       

       

      What I need  (I think) is to use interval match to join CUSTOMER_TYP on DATA to know what was the type of customer in specific date. Then i need to join TYPE_RATE on CUSTOMER_TYPE and SPECIAL_RATE  on DATA to know, what rate to use in specific date (special rate prefered, type_rate otherwise). Everything is considering Valid_from / Valid_to.

       

      Let's say, that i solved the issue of missing valid_to by this:

      IntervalMatch without an Ending Date (or max value) in match table

       

      But I have no idea, why using two interval matches doesnt work or what am I doing wrong. HOW TO INTERVALMATCH TYPER_RATE ON CUSTOMER_TYPE?

       

      My incomplete solution is in attachment. Thx for any advice...