1

How can I loop through the list of numbers (invoice and credit note values) to find all the combinations of invoices / credits that match a payment amount? I don't mind doing this in PHP, or directly in MySQL, whichever makes it most viable.

I started looking at this question but can't use python , otherwise this question would answer it : Find all combinations of a list of numbers with a given sum

See example data below (EDIT: sorry about the images at first. Is this OK now?)

invoice value
INV-1   1
INV-2   3
INV-3   1
INV-4   2
INV-5   4
INV-6   7
CRED-1  -1
CRED-2  -1
CRED-3  -1
CRED-4  -1

.

payment
5

.

possible combinations to match payment      
invoice value   set
INV-1   1   a
INV-2   3   a
INV-3   1   a
        
INV-2   3   b
INV-4   2   b
        
INV-1   1   c
INV-5   4   c
        
INV-4   2   d
INV-6   7   d
CRED-1  -1  d
CRED-2  -1  d
CRED-3  -1  d
CRED-4  -1  d
Lyndon Penson
  • 65
  • 1
  • 8
  • 2
    MySQL <> SQL Server - please correct your tags - i.e. pick one because the solution could be different - and you can easily solve it in either. – Dale K Jun 14 '22 at 01:31
  • No problem @DaleK let's go with MySQL then. The data exists in both, one for website , one for locally hosted ERP. – Lyndon Penson Jun 14 '22 at 01:40
  • 1
    This is an extremely non-trivial problem that will take orders of magnitude more time to compute as you add elements to the list. I would strongly advise using basically _any other approach_ to match invoices with payment information. – Sammitch Jun 14 '22 at 01:45
  • On SQL you must use extensive method (exhaustive search) - create bit-mapped value where each bit means that definite source row is included or not included into the combination, calculate sum for each combination, then select those combinations whose sum is equal to needed one. For your source data (10 rows) the server should calculate and investigate 2^10=1024 combinations. I'd recommend to solve this on the client side where you can use any optimized technique for "the cutting stock problem". – Akina Jun 14 '22 at 04:41
  • Thanks @Akina , please give an example of a name or technology of such a client side optimised technique? – Lyndon Penson Jun 14 '22 at 08:12
  • ??? "the cutting stock problem" – Akina Jun 14 '22 at 10:02

0 Answers0