3-step conditional Join

Wed, 07 Dec 2016 09:41:50 GMT

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

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.

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.

Re: 3-step conditional Join

sunny_talwar — Wed, 07 Dec 2016 16:26:04 GMT

I think you need to look into IntervalMatch