How to associate 2 tables with a between condition

[Anonymous]

I have 1 table of prices with start date and end date, like this:

31.12.201231.12.4100

Price_line_id	List Name	Item_id	Start_date	End_date	Price
1	List1	1	01.01.2012	10
6	List1	1	01.01.2013	15
2	List1	2	01.01.2012	31.12.4100	12
3	List2	1	01.01.2012	31.12.2012	11
5	List2	1	01.01.2013	31.12.4100	16
4	List2	3	01.01.2012	31.12.4100	20

1 other table of orders lines with a date, the price list name and the item.

Order_line	Order_date	Price List	Item_id	Qty
1	30.12.2012	List1	1	10
2	30.12.2012	List1	2	10
3	30.12.2012	List2	1	10
4	03.01.2013	List1	1	10
5	03.01.2013	List2	1	10
6	03.01.2013	List2	3	10

And i will have this result:

10

Order_line	Order_date	Price List	Item_id	Qty	Price_line_id	Price	Total
1	30.12.2012	List1	1	10	1	100
2	30.12.2012	List1	2	10	2	12	120
3	30.12.2012	List2	1	10	3	11	110
4	03.01.2013	List1	1	10	6	15	150
5	03.01.2013	List2	1	10	5	16	160
6	03.01.2013	List2	3	10	4	20	200

Easy to explain

But i don't have find a solution for linking the price line with the order line with the Order.order_date between the Prices.Start_date and Prices.End_date. 

[sushil353 · Accepted solution]

Use interval Match function for the solution

HTh

Sushil