How two records can link to one value, like a package. Possibly link table?

[Anonymous]

I have customers that have a plan and features.  If they have just the plan then is is in that package.  If they have a plan and feature then it's in a different package.  This cannot be calculated in the load script as I need to be able to select a date and it shows what the customer had at any time.

As you can see below, if the customer just has a plan they they are in one package but if they have both a plan and feature they fall into a combined package.

I am wondering if some sort of link table between the Customer SKUs and the packages but not sure how this would work.

Notice below how customer starts with plan abc and feature 123 then changes to feature 456 and finally drops feature 456.  At any date selection during those periods I would need to see the Package they had.

Example:

CustomerTable

Customer    SKU    Type              StartDt          EndDt

001              abc    Plan              6/1/2015          7/25/2015

001              123    Feature          6/1/2015          6/20/2015

001              456    Feature          6/21/2015        7/15/2015

002              abc    Plan              5/1/2015          7/10/2015

003              efg      Plan              4/2/2015          7/23/2015

003              123    Feature          4/2/2015          7/23/2015

PackageTable

SKU                Package

abc                50

efg                100

(abc + 123)    Simple 50

(abc + 456)    Simple 100

(efg + 123)    Advanced 50

Thank you very much for your help.

[Anonymous]

I do not want the dates to scare anyone.  I have that portion setup.  I just wanted to show that this could not be set as a one time grouping and needs to be linked somehow. 

Basically the Plan and Feature SKUs on two rows.  If a customer has this it links to the appropriate group.  If only the Plan SKU then it links the the appropriate group.

Thank you.

[Anonymous]

Maybe this will better explain what I am trying to do.

If a customer has the 1 SKU they are in a group but if they have both the Plan and Feature SKUs they are in a combined group.

This needs to be done in the model as this needs to be dynamic.

Thank you for your help.

[ramoncova06]

I know this not what you want to hear but the best option is to do this in the backend, you need to concatenate the sku for each customer and get what date it changed by combining result with intervalmatch

[Anonymous]

Yeah Ramon,

Not what i was hoping for.  I am guessing it would look something like this.

[Anonymous]

Ramon,

Could you expand on this a little.  I am not sure I understand what you mean.

I created a date intervalmatch table on each Plan and then joined the features back into the bridge table to then concatenate the Plan and Feature SKUs together on the individual dates they match on and it is massive.  A 2gig + file.

Ramon Covarrubias wrote:

I know this not what you want to hear but the best option is to do this in the backend, you need to concatenate the sku for each customer and get what date it changed by combining result with intervalmatch