Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have three tables (in XML) where I need to make a conditional join.
SalesTable
Date | Item ID |
---|---|
2016-03-01 | A |
ItemTable
ItemID | PriceListID |
---|---|
A | X |
PriceList
PricelistID | ValidFrom | Price |
---|---|---|
X | 2015-06-01 | 20 |
X | 2016-01-01 | 25 |
X | 2016-06-01 | 30 |
I now need to make a join to see what price the item had on 2016-03-01. something like
use PriceList.Price where max(ValidFrom) < SalesTable.Date
Is it possible to get help here?
The goal is to get the relevant sales price attached to each sales line.
ie
Date | ItemID | Price |
---|---|---|
2016-03-01 | A | 25 |
it needs to be outer joins, not all sales lines has an matching Item and not all Items has pricelistIDs
much appreciated.
I think you need to look into IntervalMatch
I think you need to look into IntervalMatch