Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have an issue combining data from production database and sales database. I need to create the full dataset concatenating those tables in the load script.
Table 1 comes from production
Production | |||
Batch | Date** | Item1* | Cost1 |
Batch | Date** | Item2* | Cost2 |
Batch | Date** | Item3* | Cost3 |
Batch | Date** | Item4* | Cost4 |
Table 2 from Sales
Sales | |||
DateFrom** | DateTo** | Item1* | Price1 |
DateFrom** | DateTo** | Item2* | Price2 |
DateFrom** | DateTo** | Item3* | Price3 |
DateFrom** | DateTo** | Item4* | Price4 |
I need to have the following table with column price. The key to link Production and sales is item and date. Item is 1-1 connection. Date is more complicated, as I have to check the date of production to which “datefrom” and “dateto” refers.
Final table | ||||
Batch | Date | Item1* | Cost1 | Price1 |
Batch | Date | Item2* | Cost2 | Price2 |
Batch | Date | Item3* | Cost3 | Price3 |
Batch | Date | Item4* | Cost4 | Price4 |
* key1
** key2 (“Date” between “Datefrom” and “DateTo”)
Can you please help me out?
You can use the intervalmach function. See this blog post: IntervalMatch