I modified the code sample. I had forgotten about the amount column in the bundle group table, which is the reward amount allocated when a bundle match is found. The requirement was indeed vague without that variable since the best possible total reward amount for the sale is what we are looking for.

The outcome could look like:

salesid product bundleid QuantityIncomingQuantityUsedRewardAmountRewardPick

1A11125No

2B12125No

1A21150Yes

3C21150Yes

4D31135Yes

5E33235Yes

6Fnullnullnullnullnull

The bundle AC got the RewardPick flag set to 'yes" over the AB bundle because of the greater reward amount. Overall, I am trying to put in place generic code to

1. "find if a combination of products (incoming sales) match any predefined sets of bundles (match by sku and quantity)"

2. After all the possible matches have been found, find the best possible reward outcome across those combinations.

I hope this makes more sense now. I am catching up on reading on permutations / combinations right now 😉 since this is first a math problem before being a SQL problem.

Thanks again for looking into it.