3-step conditional Join

[#1]

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.

[sunny_talwar · Accepted solution]

I think you need to look into IntervalMatch