/-- This code assumes that there is a database called playpen which contains a schema called play.-- It puts verious a type definition and a udf into that schema.use playpengo-- first create the type used for coin sets:create type play.coinlist as table(val int, ccount int);-- now create the UDFcreate function play.coinsums (@coins play.coinlist READONLY)returns table asreturn with tally as (select 0 as I union all select top 5000 row_number() over(order by y.object_id) from master.sys.all_objects y cross join master.sys.all_objects x), coinspad as (select * from @coins -- pad the parameter in case it has fewer than 8 rows. union all select 1000000,0 union all select 1000001,0 union all select 1000002,0 union all select 1000003,0 union all select 1000004,0 union all select 1000005,0 union all select 1000006,0 union all select 1000007,0), tempcoins as (select row_number() over (order by val) as seq, val, ccount from coinspad), A1 as (select I*val as v from tempcoins, tally where seq=1 and I between 0 and ccount), A2 as (select I*val as v from tempcoins, tally where seq=2 and I between 0 and ccount), B2 as (select distinct A1.v+A2.v as v from A1 cross join A2), A3 as (select I*val as v from tempcoins, tally where seq=3 and I between 0 and ccount), B3 as (select distinct B2.v+A3.v as v from B2 cross join A3), A4 as (select I*val as v from tempcoins, tally where seq=4 and I between 0 and ccount), B4 as (select distinct B3.v+A4.v as v from B3 cross join A4), A5 as (select I*val as v from tempcoins, tally where seq=5 and I between 0 and ccount), B5 as (select distinct B4.v+A5.v as v from B4 cross join A5), A6 as (select I*val as v from tempcoins, tally where seq=6 and I between 0 and ccount), B6 as (select distinct B5.v+A6.v as v from B5 cross join A6), A7 as (select I*val as v from tempcoins, tally where seq=7 and I between 0 and ccount), B7 as (select distinct B6.v+A7.v as v from B6 cross join A7), A8 as (select I*val as v from tempcoins, tally where seq=8 and I between 0 and ccount), B8 as (select distinct B7.v+A8.v as v from B7 cross join A8)select count(v) - 1 as answer from B8; -- subtracts one because using 0 coins is not allowedgo-- examples of using it:-declare @testlist play.coinlist;select answer from play.coinlist @testlist; -- call it with no coins, it returns 0insert @testlist(val,ccount) values(1,3),(2,2),(5,0),(10,3),(50,1); select answer from play.coinlist @testlist; -- another simple onedelete @testlist; insert @testlist(val,ccount) values(1,1),(2,5000),(5,1),(10,2),(20,1),(50,7),(100,402),(200,35);select answer from play.coinlist @testlist; -- a less simple one, with 8 rows in the parameter - the max allowed.-- I picked 8 as my maximum number of denominations because that's how many are in general us in the UK-- The UK coinage comes in units of 1,2,5,10,20,50,100 and 200 pence.insert @testlist(val,ccount) values(500,99); -- push it up to 9 rowsselect answer from play.coinlist @testlist; -- gives the wrong answer: only sees the first 8 rows in the table parameter.-- the call above gives the same answer as the one before. -- note: the UK has occassional special issues with coins which have a face value of 500 pence-- but 500 pence coins are collectors pieces, generally more valuable than 500 pence, so are not used as money.

/* Combinations of coins - generic solution by Mister Magoo */-- drop our temp tables if they existif object_id('tempdb..#values') is not null drop table #values;if object_id('tempdb..#coins') is not null drop table #coins;if object_id('tempdb..#results') is not null drop table #results;-- lets have a table for the coins - denomination and quantitycreate table #coins(den smallint primary key not null,number int not null);-- and some test datainsert #coins(den,number)values(1,3),(2,2),(5,0),(10,3),(50,1); -- should come up with 63 values/* some more test */--values(1,1),(2,5000),(5,1),(10,2),(20,1),(50,7),(100,402),(200,35); -- should come up with 57596 values--values(1,1),(2,5000),(5,1),(10,2),(20,1),(50,7),(100,402),(200,35),(500,99); -- should come up with 107096 values-- let's build a tally cte;withE1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rowsE2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rowsE4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max/* you can add more elements to the cte to handle larger numbers of coins per denomination if required */-- now generate all the possible values for each coin denominationselect den,den*n as valueinto #valuesfrom #coins coinscross apply( select 0 union all select top (coins.number) row_number() over (order by (select null)) from e4 ) ctetally(n);-- index the list so we can get each denomination's values quicklycreate clustered index ix_vals_ci on #values(den);-- create somewhere to put the results -- NOTE: the use of identity on "den" is to remove the need for an index-- we can tell what we last processed by checking scope_identity()create table #results(den int identity(1,1) not null , value int not null);-- allow direct insertes of identity valuesset identity_insert #results on;-- add the first set of results for the first coin denominationinsert #results(den,value)select den,valuefrom #valueswhere den = (Select min(den) from #values);-- now a single statement while loop to insert the rest of the results -- this works out as X inserts of sets of data where X = the number of coin denominations in the #coins table-- this is the same logic as a recursive cte, but many times fasterwhile @@rowcount>0 insert #results(den,value) -- use of distinct saves us processing duplicates as we go along select distinct vals.den,base.value + vals.value from ( -- let's get the last set of data we inserted from the #results table by using scope_identity() to identify them select distinct value from #results res where res.den = scope_identity() ) base cross join ( -- and cross join that with all the possible values of the next coin in the #coins table select den,value from #values vals where vals.den = ( select top 1 coins.den from #coins coins where coins.den> scope_identity() order by coins.den) ) vals;-- finally the results, excluding zeroselect distinct valuefrom #resultswhere value>0order by value;

;with coins(den,number) as(select den,number from (values(1,3),(2,2),(5,0),(10,3),(50,1))a(den,number)),E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rowsE2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rowsE4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max, vals(den,value) as(select den,den*nfrom coinscross apply( select 0 union all select top (coins.number) row_number() over (order by (select null)) from e4 ) ctetally(n)), combinations as(select den,valuefrom valswhere den=(select min(den) from coins)union allselect vals.den, vals.value + combi.valuefrom combinations as combicross apply ( select den,value from ( select den,value,rank() over(order by den) as rnk from vals where vals.den>combi.den ) a where a.rnk = 1 ) vals)select distinct valuefrom combinationswhere value>0

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

CREATE TABLE #coinlist (val int, ccount int);insert #coinlist(val,ccount) values(1,3),(2,2),(5,0),(10,3),(50,1); WITH UniqueCoinCombos (n, Tuples, ID) AS ( -- Return all possible combinations of the coins with cccount <> 0 SELECT 1, CAST(val AS VARCHAR(8000)), val FROM #coinlist WHERE ccount <> 0 UNION ALL SELECT 1 + n.n, CAST(t.val AS VARCHAR(8000)) + ',' + n.Tuples, val FROM UniqueCoinCombos n CROSS APPLY ( SELECT val FROM #coinlist t WHERE t.val < n.ID) t),Tally (n) AS ( SELECT 0 UNION ALL SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)-- Get the distinct number of unique sumsSELECT answer=COUNT(DISTINCT TotVal)FROM( -- Sum the coin values within a unique combination SELECT rn, n, TotVal=SUM(TotVal) FROM ( -- Split the delimited list (a combination of coin values) -- and apply a Tally table to get 0 to ccount of each SELECT rn, Item, d.n, TotVal=Item*d.n FROM ( -- Number each unique combination SELECT *, rn=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM UniqueCoinCombos ) a CROSS APPLY DelimitedSplit8K(Tuples, ',') b JOIN #coinlist c ON b.Item = c.val CROSS APPLY ( SELECT n FROM Tally WHERE n BETWEEN 0 AND c.ccount ) d ) a GROUP BY rn, n) a;GODROP TABLE #coinlist;

SELECT rn, n, TotVal=SUM(TotVal) FROM ( -- Split the delimited list (a combination of coin values) -- and apply a Tally table to get 0 to ccount of each SELECT rn, Item, d.n, TotVal=Item*d.n FROM ( -- Number each unique combination SELECT *, rn=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM UniqueCoinCombos ) a CROSS APPLY DelimitedSplit8K(Tuples, ',') b JOIN #coinlist c ON b.Item = c.val CROSS APPLY ( SELECT n FROM Tally WHERE n BETWEEN 0 AND c.ccount ) d ) a GROUP BY rn, n