2 Replies Latest reply: May 27, 2015 1:01 PM by Darrin Pilkington RSS

    Interval match - interval partitioning is not working as expected

    Darrin Pilkington

      I have been reading through henric cronström tech doc on interval match to solve an issue I have.

       

      At any particular date selected I need to be able to identify the Plan and the Features a customer has active.  Even if the Feature was active before the new plan if it's still active.

      As in Feature6 below.  It was active long before the current Plan4 but it should still display as it's still active.

       

      I have customers that have Plans and Features, each with Start and End dates.  So two table that look something like this.  Customer can have only 1 Plan at a time but many Features on that Plan at any given time.

       

      The problem I am running into is that when I attempt the interval partition my Subinterval table has no dates from any Plan or Feature that is currently active with no EndDt.  The RangeMax and RangeMin expect values or return Null.

         

      PlanSKUPlanStartDtPlanEndDt
      Plan11/11/20131/14/2013
      Plan21/15/201312/15/2013
      Plan312/15/20135/15/2015
      Plan45/15/2015

       

         

      FeatureSKUFeatureStartDtFeatureEndDt
      Feature11/11/20145/15/2015
      Feature23/15/20155/15/2015
      Feature33/15/20155/15/2015
      Feature412/15/20135/15/2015
      Feature55/15/2015
      Feature62/18/2013
      Feature75/15/2015

       

      Thank you for your help.

        • Re: Interval match - interval partitioning is not working as expected
          Marcus Malinow

          Darrin,

           

          IntervalMatch won't work on your tables as they are - you'll need day level data.

           

          To resolve this, I'd create a calendar table. Then I'd create a link table using Intervalmatch to match your Plans to the Calendar. Finally I'd use an Intervalmatch on this to join to the Features.

           

          Hope this makes sense.

           

           

          Marcus

            • Re: Interval match - interval partitioning is not working as expected
              Darrin Pilkington

              Marcus,

              Thank you for the quick response.  I think I was trying to over complicate how this would be built out.  It did seem like since there were the two date ranges it needed the partition option but really it's a single table with a different type of record.  Type = Plan or Feature.  With the interval match on the Calendar table I can pick any date and it will pull the single Plan and any Features active at that time.

               

              The only thing I had to do as I could not make this work was place a far out date for any currently active Plan or Feature.  It works but seems like its not the best solution.

               

              Example: Plan4 added future PlanEndDt

              :

              PlanSKUPlanStartDtPlanEndDt
              Plan11/11/20131/14/2013
              Plan21/15/201312/15/2013
              Plan312/15/20135/15/2015
              Plan45/15/201512/31/2016

               

              Thanks