Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have source table with the following structure:
from | to | prodID | date |
supplier1 | warehouse1 | 1 |
|
warehouse1 | warehouse2 | 1 |
|
supplier2 | warehouse1 | 1 |
|
warehouse2 | shop1 | 1 |
|
supplier3 | shop4 | 1 |
|
warehouse1 | shop3 | 1 |
|
and I want to find the last original supplier in the separate field:
from | to | prodID | date | original supplier |
supplier1 | warehouse1 | 1 |
| supplier1 |
warehouse1 | warehouse2 | 1 |
| supplier1 |
supplier2 | warehouse1 | 1 |
| supplier2 |
warehouse2 | shop1 | 1 |
| supplier1 |
supplier3 | shop4 | 1 |
| supplier3 |
warehouse1 | shop3 | 1 |
| supplier2 |
I struggle with this task for months. The difficulty is in the size of source table ( about 15M records for 15 month ) and that I can't build the tree using "hierarchy" cause I have to connect with the closest last date (not the same).
Any ideas would be appreciated.
I think there's not enough information to solve this reliably. How can we tell if a value in the from column is a supplier? How can we tell what is the start and the end of a supply run?
We can define suppliers, shops, and warehouses based on mask (warehouse id starts with '0', shop - '1', suppliers - 2..9).
We assume that supply starts from 'date' field and ends when we get the next supply.