I always enjoy reading the articles, but this one got me a bit stumped. Where would you actually use this?:unsure:
Oh my! So it's practical that you seek.
In my case, I had a finite set of events and I needed to know the all the possible combinations of those events that could occur regardless of sequencing and without recurrance. A separate problem (much easier to solve incidentally in a set-based fashion) was to find out which events had actually occurred.
Another example would be a grocer who wishes to assemble for sale gift baskets consisting of various combinations of items that fall within a total cost range (so that he can sell the basket for a specific price and a profit margin). All you need to do is add your cost column and limit the results to those combinations that meet the combined cost criteria. Or add additional criteria to consider number of items (e.g., no less than 6) or cost/profit balancing.
Or how about a more fun example? I am a thief and I've just entered a vault filled with valuable antiques. My cunning thief-like brain can easily establish the intrinsic value of each of these items (or at least amount that I know my cheap fence will pony up) but alas, my knapsack (and my old weary bones) can carry no more than a certain weight of items. My task is to steal the highest value of booty that I can carry out of there. Yes! The classic knapsack problem of dynamic programming. Assign your weight and value to each of the products and you can use this solution to solve the problem in a brute force fashion (assuming of course that there aren't too many items to choose from).
These are examples of classic allocation problems and of course, this isn't the dynamic programming solution to this particular problem, but we'll have to leave that for another day.
And yes, Jeff these solutions probably aren't the fastest solutions out there. The chalenge is to solve an intrinsically procedural solutions with a declarative program. And when recursive approaches exist, it is often possible to do so with a recursive CTE. (I know, just because something can be done doesn't mean that it should be done).
Consider though that solutions of this nature are not likely to be running in production nightly. They're more often one-offs.
And most of all, thanks for taking the time to take a look and share your thoughts!
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]