Finding where a date lies in a date range and provide corresponding value

[WW7350]

Hey! I was wondering if someone could help me, been trying to figure this out for ages!!

I have this table: (Table called Quantity)

Component ID	Purchase Date	Quantity
625656561	18/05/2012	2
625656561	20/06/2012	5
625656561	16/07/2012	8
784891	18/01/2012	9

and this table: (The valid To and valid From refers to when the prices are valid) (Table called Price)

Component ID	Valid To	Valid From	Price
625656561	01/04/2012	19/05/2012	16
625656561	19/06/2012	25/06/2012	26
625656561	29/06/2012	01/07/2012	32
784891	01/01/2012	31/01/2012	51
784891	12/02/2012	19/02/2012	21

So im trying to get a query that finds the corresponding price when the component was bought so I can times the price by the quantity.

To get:

Component ID	Quantity	Price
625656561	2	16
625656561	5	26
625656561	8	32
784891	9	51

If anyone has an ideas that would be great THANKS!

[recyan]

625656561 8 32

Wondering how you got above in result ?

Just check if below gives some guidelines :

SELECT 
    Quantity.[Component ID], 
    Quantity.[Purchase Date], 
    Quantity.Quantity, 
    Price.[Component ID], 
    Price.[Valid To], 
    Price.[Valid From], 
    Price.Price
FROM 
    Quantity 
    LEFT JOIN 
    Price 
    ON 
    (Quantity.[Component ID] = Price.[Component ID])
    AND 
    (
        Quantity.[Purchase Date] 
        BETWEEN 
        Price.[Valid From]  AND Price.[Valid To]
    );

In addition, check out :

http://www.baldyweb.com/OverLap.htm

Thanks

[WW7350]

Thanks recyan!! I had to change a few things, but it now works!!! Thanks very much!!!!

[recyan]

Glad you got things working.

Thanks