Solutions for T-SQL Challenge - Combinations

Last week I provided a challenge to return all distinct sums of amounts that can be produced by combining voucher amounts. You can find the details of the puzzle here. Some of the solutions that I got only work with a very small number of vouchers, and some assumed that the voucher ids were consecutive. I did not restrict the puzzle to any max number of supported vouchers, and also I didn’t say that the voucher ids are guaranteed to be consecutive. Perhaps the following sample data would be better both because it contains more rows, and the voucher ids are not consecutive:

You can come up with fairly simple solutions if you don’t aim at good performance. The basic idea is to calculate the sums of all possible combinations of vouchers without pruning duplicates in the process, and finally select the distinct sums. For example, the following solution with very similar versions written by Dieter Noeth and myself:

WITH PowerSet AS ( SELECT amt, vid AS last_vid FROM dbo.Vouchers UNION ALL SELECT P.amt + V.amt, V.vid FROM PowerSet AS P JOIN dbo.Vouchers AS V ON V.vid > P.last_vid ) SELECT DISTINCT amt FROM PowerSet ORDER BY amt;

The problem with this approach is that it is extremely inefficient—for n vouchers, there are 2^n possible combinations. So beyond a very small number of vouchers, the number of combinations can get quite large. For example, with the 150 vouchers in the new sample data I provided here, there are 2^150 possible combinations (1427247692705959881058285969449495136382746624). The following solutions by Alexey Gasperovich, Zsolt Soczo and Rob Farely also do not prune duplicates in the process rather only at the end. All three solutions support only a small number of vouchers, and the solutions by Zsolt and Rob assume that the voucher ids start with 1 and are consecutive:

-- Alexey Gasperovich select distinct SUM(t.amt) as amt from master..spt_values as n join ( select POWER ( 2 , row_number() over ( order by vid ) - 1) as ex, amt from dbo.Vouchers ) as t on t.ex & n.number > 0 where n.type = 'p' group by n.number order by amt -- Zsolt Soczo with Number(Num) as (select 1 as Num union all select Num + 1 from Number where Num

Notice that even though the three solutions might seem similar on the surface, Rob’s solution doesn’t require an auxiliary table of numbers (potentially, a huge one) like the other two. If you do care about performance, you would have to prune duplicates in the process. Following are several solutions that do so and run under a minute on my laptop with the new sample data I provided here. The fastest was provided by Steve Kass, and runs for only 0.2 seconds!

From the Blogs

The quest for the Golden Record to achieve a single, accurate and complete version of a customer record is worth the pursuit to attain survivorship. Record matching and consolidation are only the beginning. Melissa Data takes a new approach. Learn how to apply intelligent rules based on reference data to make smarter and better decisions for data cleansing....More

On SQL Servers where Availability Groups (or Mirroring) isn’t in play, I typically recommend keeping a combination of on-box backups along with copying said backups off-box as well. Obviously, keeping databases AND backups on the SAME server is the metaphorical equivalent of putting all of your eggs in one basket – and therefore something you should avoid like the plague....More

One of the biggest strengths of AlwaysOn Availability Groups is that they allow DBAs to address both high availability and disaster recovery concerns from a single set of tooling or interfaces. But, this doesn’t mean that you won’t still need backups....More