The Godfather
New Member
- Joined
- Jul 22, 2011
- Messages
- 30
- Office Version
- 365
- 2016
- Platform
- Windows
Dear all,
I have a task to match invoice and payments related to the same clearing documents with FIFO method. I tried it with Excel however could not succeed. MS Access might be the solution however I have quite limited SQL experience. A very simple dataset consists up of invoices and payments is this;
Invoices table
Payments table
And the desired outcome is to obtain which portion of invoice is paid as of which date. Like this;
As you may see in the desired outcome,
1) For clearing no. 222; since there is only one invoice and there is only one payment which are matching in amount (7.500); the result is so smooth.
2) For clearing no. 111; it is a bit more complicated.
a) Invoice 1 and 2 (each 25 K) was paid with payment 7 (50 K). In the desired outcome we see that half of payment 7 (25 K) is attribute to invoice 1; and the other half is attributed to invoice 2.
b) As per FIFO, Payment 8 (30 K) first pays invoice 3 (25 K), and then pays the 5K portion of invoice 4. The remaining of invoice 4 is paid by payment 9.
So basicly; for under each clearing item and based on FIFO; I am trying to match invoices and payments according to the two rules I mentioned above.
It does not matter if the total amount of invoices and total amount of payments under the same clearing chunk is not the same (ie; does not result in 0). The code should run until either total invoice or total payment amount in that chunk runs to 0.
However I cannot formulate it in SQL.
Any help is appreciated.
I have a task to match invoice and payments related to the same clearing documents with FIFO method. I tried it with Excel however could not succeed. MS Access might be the solution however I have quite limited SQL experience. A very simple dataset consists up of invoices and payments is this;
Invoices table
| ClearingDocument | DocumentNo | DocType | DocDate | Amount |
111 | 1 | Invoice | 24.10.2022 | 25.000 |
111 | 2 | Invoice | 25.10.2022 | 25.000 |
111 | 3 | Invoice | 26.10.2022 | 25.000 |
111 | 4 | Invoice | 27.10.2022 | 25.000 |
222 | 13 | Invoice | 27.10.2022 | 7.500 |
Payments table
| ClearingDocument | DocumentNo | DocType | DocDate | Amount |
111 | 7 | Payment | 01.01.2023 | 50.000 |
111 | 8 | Payment | 01.02.2023 | 30.000 |
111 | 9 | Payment | 01.03.2023 | 30.000 |
222 | 15 | Payment | 02.03.2023 | 7.500 |
And the desired outcome is to obtain which portion of invoice is paid as of which date. Like this;
| ClearingDocument | DocumentNo | DocType | DocDate | Amount | InvoiceAmountPaid | PaymentDate | PaymentDocNo |
111 | 1 | Invoice | 24.10.2022 | 25.000 | 25.000 | 01.01.2023 | 7 |
111 | 2 | Invoice | 25.10.2022 | 25.000 | 25.000 | 01.01.2023 | 7 |
111 | 3 | Invoice | 26.10.2022 | 25.000 | 25.000 | 01.02.2023 | 8 |
111 | 4 | Invoice | 27.10.2022 | 25.000 | 5.000 | 01.02.2023 | 8 |
111 | 4 | Invoice | 27.10.2022 | 25.000 | 20.000 | 01.03.2023 | 9 |
222 | 13 | Invoice | 27.10.2022 | 7.500 | 7.500 | 02.03.2023 | 15 |
As you may see in the desired outcome,
1) For clearing no. 222; since there is only one invoice and there is only one payment which are matching in amount (7.500); the result is so smooth.
2) For clearing no. 111; it is a bit more complicated.
a) Invoice 1 and 2 (each 25 K) was paid with payment 7 (50 K). In the desired outcome we see that half of payment 7 (25 K) is attribute to invoice 1; and the other half is attributed to invoice 2.
b) As per FIFO, Payment 8 (30 K) first pays invoice 3 (25 K), and then pays the 5K portion of invoice 4. The remaining of invoice 4 is paid by payment 9.
So basicly; for under each clearing item and based on FIFO; I am trying to match invoices and payments according to the two rules I mentioned above.
It does not matter if the total amount of invoices and total amount of payments under the same clearing chunk is not the same (ie; does not result in 0). The code should run until either total invoice or total payment amount in that chunk runs to 0.
However I cannot formulate it in SQL.
Any help is appreciated.
