Hello all,
I have to combine the fields of 2 files. One of them has the invoice positions included and the other the historic article changes.
In the historical article changes are stored the Material prices of the invoiced articles. The goal is to link the articles in the invoice positions to the valid Material price defined with the article no. and the "valid from" date.
As first I have try to use the "LEFT JOIN" funktion but that doesn't work because I don't have 2 equal key fields.
The relation of the both filed should in my opnion
"Invoice position.Articleno" = " historic article changes.Articleno" and
"Invoice position.invoice date" >= "historic article changes.Valid from" and
only the first date of the "historic article changes.Valid from" that is less or equal have to be taken.
Have anybody an idea how I can solve this problem?
Thanks in advance!
Example:
Invoice position historic article changes
Invoice no, Articleno, invoice date, invoice qty Articleno, Material price, Valid from
1234 A 01.03.18 5 A 5,00 15.02.18
1234 B 15.03.18 2 B 4,00 01.03.18
5678 A 01.04.18 8 A 6,50 10.03.18
5678 B 05.04.18 7 B 5,50 01.04.18
I would like to have the following sollution:
(the sort of the file doesn't matter, I have sort thew only for the example)
Invoice position
Invoice no, Articleno, invoice date, invoice qty, Material price, Valid from
1234 A 01.03.18 5 5,00 15.02.18
5678 A 01.04.18 8 6,50 10.03.18
1234 B 15.03.18 2 4,00 01.03.18
5678 B 05.04.18 7 5,50 01.04.18
