Hi all,
Been trying to write a bit of code today to take a list of amounts and calculate any possible sum of amounts. I've got so far but unfortunately it gives me duplicate amounts for the same amounts summed in a different order... ie:
1 + 2 + 3 = 6
1 + 3 + 2 = 6
3 + 1 + 2 = 6
...
And so on. I can't for the life of me figure out how to only return a single version of this 'permutaion' (not sure that's the right word?)
The code I'm using at the moment is attached below.
If anyone gets a moment to look this over and suggest anything it'd be much appreciated.
Thanks in advance <3
Been trying to write a bit of code today to take a list of amounts and calculate any possible sum of amounts. I've got so far but unfortunately it gives me duplicate amounts for the same amounts summed in a different order... ie:
1 + 2 + 3 = 6
1 + 3 + 2 = 6
3 + 1 + 2 = 6
...
And so on. I can't for the life of me figure out how to only return a single version of this 'permutaion' (not sure that's the right word?)
The code I'm using at the moment is attached below.
If anyone gets a moment to look this over and suggest anything it'd be much appreciated.
Thanks in advance <3
Code:
DROP TABLE #perms, #amounts
;WITH Names(Name) As
(
SELECT CAST(ref_id AS VARCHAR(MAX))
FROM worldpay.[dbo].[tbl_cc_refunds]
WHERE processed IS NULL
), R(Name,Lvl) AS
(
SELECT CAST(',' + Name AS VARCHAR(MAX)), 1
FROM Names
UNION ALL
SELECT R.Name + ',' + N.Name, Lvl + 1
FROM R JOIN Names N ON R.Name + ',' NOT LIKE '%,' + N.Name + ',%'
)
SELECT STUFF(Name,1,1,'') AS Name
INTO #perms
FROM R
ORDER BY Lvl, Name
DECLARE @id AS nvarchar(max)
DECLARE @sql AS nvarchar(max)
CREATE TABLE #amounts
(
ref_id nvarchar(max),
amount money
)
DECLARE cur CURSOR
FOR
SELECT Name
FROM #perms
OPEN cur
FETCH NEXT FROM cur
INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'INSERT INTO #amounts (ref_id, amount)
SELECT ''' + @id + ''', SUM(CAST(amount AS money))
FROM worldpay.[dbo].[tbl_cc_refunds]
WHERE ref_id IN (' + @id + ')'
print @sql
exec (@sql)
FETCH NEXT FROM cur
INTO @id
END
SELECT FROM #amounts
order by amount