SQLTeam.com | Weblogs | Forums

Need to get the total matching by adding different rows by permutation combination

Transact-SQL
#1

HI All,

I need your assistance to write a script to get the match value between 2 tables

table 1
INvoice Amount
1234 500

Table2
INvoice amount
1234 25
1234 75
1234 100
1234 175
1234 180
1234 100

where in the above example i need to find the exact match for Table 1 by adding the table 2 values.(rows 1,3,4,6)

i need to perform all kind of combination search and find the rows whose sum will match the first table amount

#2

Sql query might not be the tool for this? Maybe python?

lst = [25, 75, 100, 175, 180, 100, 200]

import itertools
print("Without replacements:")
for l in range(1,len(lst)+1):
    for comb in itertools.combinations(lst,r=l):
        if sum(comb) == 500:
            print(comb)
1 Like
#3

hope this helps ..

#4

@harishgg1
Thanks for the solution, But here i need to find the possibilities in rows and find the combination by summing the values from table2 that matches the total value in table1
to be clear with example
table1

col1
500

Table 2
col1
25
75
100
175
180
100

i need to find out that Which combination of sum will be equals to 500 in table 1

adding to that i need to store the Row numbers of all that we summed to get the total