Hi guys
I've given layout of an excel file below which has three sheets out of which "invoice" and "packaging" sheets are inputs whilst the "output" sheet contains the desired output I want.
I'm finding it difficult to frame an query for the same - It'll be great if someone can help me on that.
Thanks in Advance.
PACKING SHEET-
| Packing List No | Unique ID | Packing Date | Customer Name | Item Name | QTY | Rate | Value | SO No |
| 1 | 1x | 01-04-19 | A | x | 100 | 3 | 300 | 1 |
| 2 | 1y | 01-04-19 | A | y | 200 | 6 | 1200 | 1 |
| 3 | 2z | 01-06-19 | B | z | 300 | 5 | 1500 | 2 |
| 4 | 2z | 01-08-19 | B | z | 100 | 5 | 500 | 2 |
| 5 | 1x | 01-08-19 | A | x | 100 | 3 | 300 | 1 |
| 6 | 1y | 01-08-19 | A | y | 300 | 6 | 1800 | 1 |
| 7 | 3z | 15-09-19 | B | z | 200 | 4 | 800 | 3 |
| 8 | 3z | 30-09-19 | B | z | 500 | 4 | 2000 | 3 |
| 9 | 4a | 17-09-19 | C | a | 300 | 7 | 2100 | 4 |
| 10 | 4a | 29-09-19 | C | a | 300 | 7 | 2100 | 4 |
INVOICE SHEET-
| Invoice No | Unique ID | Invoice Date | Customer Name | Item Name | QTY | Rate | Value | SO No |
| 1 | 1x | 01-05-19 | A | x | 80 | 3 | 240 | 1 |
| 1 | 1y | 01-05-19 | A | y | 150 | 6 | 900 | 1 |
| 2 | 2z | 01-07-19 | B | z | 200 | 5 | 1000 | 2 |
| 3 | 1x | 01-09-19 | A | x | 100 | 3 | 300 | 1 |
| 4 | 1y | 01-09-19 | A | y | 350 | 6 | 2100 | 1 |
| 5 | 2z | 01-09-19 | B | z | 150 | 5 | 750 | 2 |
| 6 | 3z | 15-09-19 | B | z | 180 | 4 | 720 | 3 |
| 7 | 3z | 01-10-19 | B | z | 495 | 4 | 1980 | 3 |
| 8 | 4a | 30-09-19 | C | a | 600 | 7 | 4200 | 4 |
DESIRED OUTPUT-
| SO No | Invoice No | Packing List No | Item Name | Invoice Date | Packing Date | Invoiced Quantity |
| 1 | 1 | 1 | x | 01-05-19 | 01-04-19 | 80 |
| 1 | 1 | 1 | x | 01-09-19 | 01-08-19 | 20 |
| 1 | 1 | 1 | x | 01-09-19 | 01-08-19 | 80 |
| 1 | 1 | 2 | y | 01-05-19 | 01-04-19 | 150 |
| 1 | 4 | 2 | y | 01-09-19 | 01-08-19 | 50 |
| 1 | 4 | 6 | y | 01-09-19 | 01-08-19 | 300 |
| 2 | 2 | 3 | z | 01-07-19 | 01-06-19 | 200 |
| 2 | 5 | 3 | z | 01-09-19 | 01-06-19 | 100 |
| 2 | 5 | 4 | z | 01-09-19 | 01-08-19 | 50 |
| 3 | 6 | 7 | z | 15-09-19 | 15-09-19 | 180 |
| 3 | 7 | 7 | z | 01-10-19 | 15-09-19 | 20 |
| 3 | 7 | 8 | Z | 01-10-19 | 30-09-19 | 475 |
| 4 | 8 | 9 | a | 30-09-19 | 17-09-19 | 300 |
| 4 | 8 | 10 | a | 30-09-19 | 29-09-19 | 300 |
METADATA- SO is Sales Order | The output is based on FIFO Logic | Packaging stage is primary whilst Invoice stage is secondary
