CODEWITHSUNDEEP
sqlsql-server-2008
JsonStatham
JsonStatham

Reputation: 10354

Check to see if any combination of results when added will equal a variable

I need a select statement which will interrogate a list of totals and work out whether an adding combination exists within the result set that is equal to a local variable.

Example:

create table #mytemptable
(
totals Decimal (19,2)
)

insert into #mytemptable (totals)
values (57.83),
            (244.18),
            (239.23), 
            (227.79),
            (563.12)  

select *
from #mytemptable

enter image description here

I would now like to check if any combination(s) within the result when added will equal 

285.62

Also, it would be nice if there were multiple instances where totals could be added to equal my variable then this would be handled and displayed in an appropriate fashion.

Upvotes: 2

Views: 140

Answers (2)

Tobsey
Tobsey

Reputation: 3400

If you are willing to add an identity column to your table the following CTE solution will work for you:

WITH SumOfPermutations AS
(
  SELECT
    CONVERT(decimal(15,2), 0) SummedTotals,
    0 id
  UNION ALL
  SELECT
    CONVERT(decimal(15,2), A.SummedTotals + B.totals),
    B.ID
  FROM
    SumOfPermutations A
    INNER JOIN myTempTable B ON A.ID < B.ID AND A.SummedTotals + B.Totals <= 285.62
  WHERE
    A.SummedTotals + B.totals <= 285.62    
  )
SELECT
  COUNT(*) 
FROM 
  SumOfPermutations
WHERE 
  SummedTotals = 285.62

However be advised if you have a large number of small values the performance will degrade massively. This is because of the fact that once a permutation's sum is above 285.62 it is not included any more. If you have lots of small small values then you will have lots of permutations that have a large number of values before they reach the 285.62 threshold. If your real data is distibuted similarly to the example data you gave, this should work well and quickly.

If you expect that the most numbers from your table that can be summed to a value below your 285.62 is of the order of 10, you should be OK. However if you have 20 values in your table below 30.0 you will probably have issues with this.

Upvotes: 0

Joao Leal
Joao Leal

Reputation: 5542

A bit convoluted but here it goes:

Basically my aim is to generate a dynamic query where one column will identify the value (first value on column A, second on column B, etc) and finally a column with the total.

After that we can do a group by with cube, which will sum all the permutations of values grouping them by the different columns. view example

The final result will show something like:

Total   A     B  C   D  E
285.62 NULL NULL NA NA NA

This will indicate that 285.62 is the sum of the first and 2nd values, sorted by value

DECLARE @columns varchar(max) = ''
DECLARE @allcolumns varchar(max) = ''
DECLARE @columnName varchar(1) = 'A'
DECLARE @select varchar(max) = ''

SELECT 
    @columns = @columns + ',''NA'' AS ' + @columnName, 
    @allcolumns = @allcolumns + ',' + @columnName,
    @columnName = CHAR(ASCII(@columnName)+1)
FROM
#mytemptable

SET @columnName = 'A'

SELECT
@select = @select + CHAR(13) + 'UNION SELECT ' + CONVERT(varchar(100),totals) + ' AS totals' + STUFF(@columns,2+10*(ord-1),4,'''' + @columnName + ''''), @columnName = CHAR(ASCII(@columnName)+1)
FROM
(SELECT totals, ROW_NUMBER() OVER(ORDER BY totals) ord from #mytemptable)
A



SET @select = STUFF(@select,1,6,'')
SET @allcolumns = STUFF(@allcolumns, 1,1,'')

--PRINT (@select)
EXEC ( 'SELECT * FROM (
        SELECT SUM(totals) AS Total, ' + @allcolumns + ' 
        FROM (' + @select + ') A GROUP BY ' + @allcolumns + ' WITH CUBE
        ) sub WHERE Total = 285.62 ')

Upvotes: 2

Related Questions